0

How do I update a column in multiple rows to something different in one SQL query while ignoring rather than inserting rows with keys that are not duplicates?

In this little example "column3" is the column that should be updated. So if a row with id 5 does not exist, then it should not be inserted. Using INSERT ON DUPLICATE KEY UPDATE will insert those that do not exist, so this will not work. Note there may be many rows to be updated. dbfiddle: https://www.db-fiddle.com/f/vhqJXYFy52xRtVBc97R1EL/0

CREATE TABLE t1 (
id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
column2 INT NOT NULL,
column3 INT NOT NULL);

INSERT INTO t1
VALUES
(1, 1, 10),
(7, 2, 20);

INSERT INTO t1 (id,column2,column3) 
VALUES 
(1,0,2),
(5,0,3),
(7,0,4)
  ON DUPLICATE KEY UPDATE column3=VALUES(column3);
 
SELECT * FROM t1;
John
  • 41
  • 1
  • 7
  • No. I have already seen it. There is an answer that uses the solution that doesn't work and the other answers use case which I would imagine is extremely inefficient when there are a large number of rows to insert (when adding a case for each id, which are all unique). – John Oct 18 '21 at 15:36

1 Answers1

2

If your second dqata a table like here you can join both with the correct ON clauswe

Schema (MySQL v5.7)

CREATE TABLE t1 (
id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
column2 INT NOT NULL,
column3 INT NOT NULL);

INSERT INTO t1
VALUES
(1, 1, 10),
(7, 2, 20);

UPDATE t1 
JOIN ( SELECT
1 AS id,0 AS col2,2 AS col3 UNION
SELECT 5,0,3 UNiON
SELECT 7,0,4) t2 ON t2.id = t1.id
 
 SET t1.column3 = t2.col3;

Query #1

SELECT * FROM t1;
id column2 column3
1 1 2
7 2 4

View on DB Fiddle

nbk
  • 45,398
  • 8
  • 30
  • 47
  • What exactly is t2? – John Oct 18 '21 at 15:42
  • 1
    it is the same data that you tried to insert on duplicate, a join needs a table. so i produced one out of your data – nbk Oct 18 '21 at 15:43
  • Is doing a SELECT and UNION for each row to be inserted like this going to be reasonably performant, that is, not terribly inefficient? – John Oct 18 '21 at 15:51
  • Also is there a way to do the column naming "1 AS id,0 AS col2,2 AS col3" before the row definitions? – John Oct 18 '21 at 15:56
  • usually you create temporay table and buklk insert the data and use this as joinig table. UNION works only n a few select the number is deterministic – nbk Oct 18 '21 at 16:02