更新和增加重复的多列 [英] 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)

第一次运行这个查询应该用新值添加 (+) column 现有值并用 1 增加现有 column-C 值，让我试着用一些来解释例子

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

以下是默认表，其中 price 是唯一列，其余列的默认值为 0

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

<头>

价格	stock1	stock1-C	stock2	stock2-C

运行以下值会创建这样的行

Running following values creates rows like this

('99', '10', 0), 
('120', 0, '12')

<头>

价格	stock1	stock1-C	stock2	stock2-C
99	10	0	0	0
120	0	0	12	0

再次运行相同的值应该使用新值添加 column 并增加 column-C 列

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')

<头>

价格	stock1	stock1-C	stock2	stock2-C
99	20	1	0	0
120	0	0	24	1

预期结果:

以上两个查询非常适合我的代码，现在让我们尝试插入重复的价格

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

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

既然没有 150 的价格，它应该给出:

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

<头>

价格	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')

<头>

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

实际结果:

但是第一次用 1 更新行，下次用 3 更新

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

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

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

<头>

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

第二次跑步时我会这样

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

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

<头>

价格	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:我有 50 多只股票要在单个查询中更新，这就是为什么要优化我的代码，谢谢.

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

推荐答案

由于列不可为空，您必须为列不为空的列必须传递 0s 而不是 nulls您不想在 INSERT 语句中提供值并使用与 0 的比较而不是 null:

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)

结果:

<头>

价格	stock1	stock1-C	stock2	stock2-C
99	10	0	0	0
120	0	0	12	0

再说一遍:

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)

结果:

<头>

价格	stock1	stock1-C	stock2	stock2-C
99	20	1	0	0
120	0	0	24	1

那么:

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)

结果:

<头>

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

那么:

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)

结果:

<头>

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

请参阅演示.

这篇关于更新和增加重复的多列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！