CODEWITHSUNDEEP
mysqlsql
Gracie williams
Gracie williams

Reputation: 1145

Update and Increment multiple columns on duplicate

I have following query to insert multiple values and on duplicate need to update the columns

 INSERT INTO stock (price, stock1, stock2) VALUES
  ('99', '10', 0), 
  ('120', 0, '10')
ON DUPLICATE KEY UPDATE  
  stock1 = COALESCE(stock1 + VALUES(stock1), stock1), 
  `stock1-C` = `stock1-C` + (VALUES(stock1) IS NOT NULL),
  stock2 = COALESCE(stock2 + VALUES(stock2), stock2), 
  `stock2-C` = `stock2-C` + (VALUES(stock2) IS NOT NULL)

Running this query first time should add (+) column existing value with new value and increment existing column-C value with 1 , let me try to explain with some examples

Following is the default table , where price is the unique column and remaining columns have default value 0

price stock1 stock1-C stock2 stock2-C

Running following values creates rows like this

('99', '10', 0), 
('120', 0, '12')
price stock1 stock1-C stock2 stock2-C
99 10 0 0 0
120 0 0 12 0

Running the same values like this again should add column with new value and increment the column-C column

('99', '10', 0), 
('120', 0, '12')
price stock1 stock1-C stock2 stock2-C
99 20 1 0 0
120 0 0 24 1

Expected Result :

Above two query works perfectly for my code , now lets try inserting duplicate price

('150', '55', 0), 
  ('150', 0, '56')

Since there is no 150 in price before it should give :

price stock1 stock1-C stock2 stock2-C
99 20 1 0 0
120 0 0 24 1
150 55 0 56 0

And running again should update rows like :

 ('150', '10', 0), 
 ('150', 0, '10')
price stock1 stock1-C stock2 stock2-C
99 20 1 0 0
120 0 0 24 1
150 65 1 66 1

Actual Result :

But first time rows are getting updated with 1 and next time its updated with 3

('150', '55', 0), ('150', 0, '56')

price stock1 stock1-C stock2 stock2-C
99 20 1 0 0
120 0 0 24 1
150 55 1 56 1

And 2nd time when I run I get like this

('150', '55', 0), ('150', 0, '56')

price stock1 stock1-C stock2 stock2-C
99 20 1 0 0
120 0 0 24 1
150 55 3 56 3

Whereas If i do that in multiple query it works fine

INSERT INTO `bankvolume` (`price`, `stock1`) VALUES (100, 10) ON DUPLICATE KEY UPDATE `stock1` = COALESCE(`stock1` + VALUES(`stock1`), `stock1`), 
  `stock1-C` = `stock1-C` + (VALUES(`stock1`) IS NOT NULL);


INSERT INTO `bankvolume` (`price`, `stock2`) VALUES (100, 10) ON DUPLICATE KEY UPDATE `stock2` = COALESCE(`stock2` + VALUES(`stock2`), `stock2`), 
  `stock2-C` = `stock2-C` + (VALUES(`stock2`) IS NOT NULL);

I want to combine both into single ,

P.s : I have more than 50 stocks to update in single query , that is why trying to optimise my code , thank you.

Upvotes: 1

Views: 122

Answers (1)

forpas
forpas

Reputation: 164089

Since the columns are not nullable you must pass 0s instead of nulls for the columns that you don't want to supply values in the INSERT statements and use comparisons to 0 instead of null:

INSERT INTO stock (price, stock1, stock2) VALUES
  ('99', '10', 0), 
  ('120', 0, '12')
ON DUPLICATE KEY UPDATE 
  `stock1-C` = `stock1-C` + (VALUES(stock1) <> 0 AND stock1 <> 0),
  stock1 = stock1 + VALUES(stock1), 
  `stock2-C` = `stock2-C` + (VALUES(stock2) <> 0 AND stock2 <> 0),
  stock2 = stock2 + VALUES(stock2)

Result:

price stock1 stock1-C stock2 stock2-C
99 10 0 0 0
120 0 0 12 0

Then again:

INSERT INTO stock (price, stock1, stock2) VALUES
  ('99', '10', 0), 
  ('120', 0, '12')
ON DUPLICATE KEY UPDATE 
  `stock1-C` = `stock1-C` + (VALUES(stock1) <> 0 AND stock1 <> 0),
  stock1 = stock1 + VALUES(stock1), 
  `stock2-C` = `stock2-C` + (VALUES(stock2) <> 0 AND stock2 <> 0),
  stock2 = stock2 + VALUES(stock2)

Result:

price stock1 stock1-C stock2 stock2-C
99 20 1 0 0
120 0 0 24 1

Then:

INSERT INTO stock (price, stock1, stock2) VALUES
  ('150', '55', 0), 
  ('150', 0, '56')
ON DUPLICATE KEY UPDATE 
  `stock1-C` = `stock1-C` + (VALUES(stock1) <> 0 AND stock1 <> 0),
  stock1 = stock1 + VALUES(stock1), 
  `stock2-C` = `stock2-C` + (VALUES(stock2) <> 0 AND stock2 <> 0),
  stock2 = stock2 + VALUES(stock2)

Result:

price stock1 stock1-C stock2 stock2-C
99 20 1 0 0
120 0 0 24 1
150 55 0 56 0

Then:

INSERT INTO stock (price, stock1, stock2) VALUES
  ('150', '10', 0), 
  ('150', 0, '10')
ON DUPLICATE KEY UPDATE 
  `stock1-C` = `stock1-C` + (VALUES(stock1) <> 0 AND stock1 <> 0),
  stock1 = stock1 + VALUES(stock1), 
  `stock2-C` = `stock2-C` + (VALUES(stock2) <> 0 AND stock2 <> 0),
  stock2 = stock2 + VALUES(stock2)

Result:

price stock1 stock1-C stock2 stock2-C
99 20 1 0 0
120 0 0 24 1
150 65 1 66 1

See the demo.

Upvotes: 1

Related Questions