add batch remove batch split batch comment selection show hidden batches hide batch highlight batch
db<>fiddle
donate feedback about
By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE stock(
price integer primary key,
stock1 integer default 0,
`stock1-C` integer default 0,
stock2 integer default 0,
`stock2-C` integer default 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)
SELECT * FROM stock
price 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)
SELECT * FROM stock
price 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)
SELECT * FROM stock
price 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)
SELECT * FROM stock
price stock1 stock1-C stock2 stock2-C
99 20 1 0 0
120 0 0 24 1
150 65 1 66 1