Insert into on duplicate key query for custom update on each row-CodePudding

I can insert/update data based on the following query on a MySQL server.

INSERT INTO users (user_id, books) 
VALUES 
(1, “book1, book2”), 
(2, “book3, book4”) 
ON DUPLICATE KEY UPDATE books=“book1, book2”;

However, is there a way to set multiple books column values for each row after the UPDATE statement? Something like below but that works :)

INSERT INTO users (user_id, books) 
VALUES 
(1, “book1, book2”), 
(2, “book3, book4”) 
ON DUPLICATE KEY UPDATE books 
VALUES (“book1, book2”), (“book3, book4”);

If this is not the right approach for this purpose, how should I structure such queries?

Many thanks for any guidance in advance,

Doug

CodePudding user response：

I assume that the duplicate key is the column user_id.

You can use a CASE expression:

INSERT INTO users (user_id, books) VALUES 
(1, 'book1, book2'), 
(2, 'book3, book4') 
ON DUPLICATE KEY UPDATE 
books = CASE user_id
  WHEN 1 THEN 'book10, book20'
  WHEN 2 THEN 'book30, book40'
END;

See a simplified demo.