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;

CodePudding user response：

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;

View on DB Fiddle