插入每行重复的密钥查询以进行自定义更新

发布于 2025-01-24 11:05:23 字数 498 浏览 0 评论 0原文

我可以根据MySQL Server上的以下查询插入/更新数据。

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

但是，是否有一种方法可以在更新语句之后为每一行设置多个书籍列值？下面类似但有效的东西:)

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

如果这不是为此目的的正确方法，我应该如何构建此类查询？

，非常感谢您提前的指导

多格

原文

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

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

一腔孤↑勇 2025-01-31 11:05:23

我假设重复键是列user_id。

您可以使用case表达式：

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;

请参阅简化的 demo 。
。

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.

回复收藏 0 原文

~没有更多了~