MySQL 更新难题

发布于 2024-11-24 06:57:49 字数 514 浏览 1 评论 0原文

我有一个名为 Advert_images 的表。在此表中，每个 advert_id 可能有几条记录。 Advert_images 表中的每条记录都有一个名为“image_id”的唯一字段。例如，

image_id   advert_id   main
---------------------------
1          1           0
2          1           0
3          1           0
4          2           0
5          2           0

我想要做的是：

对于每个不同的 advert_id，仅在第一条记录（最低 image_id）上将名为 main 的字段更新为 1。 IE：我想更新 image_id 为 1 和 4 的记录，并将主字段设置为 1。

原文

I have a table called Advert_images. In this table, I may have several records for each advert_id. Each record in the Advert_images table has a unique field called "image_id". e.g.

image_id   advert_id   main
---------------------------
1          1           0
2          1           0
3          1           0
4          2           0
5          2           0

What I want to do is:

Update the field called main to 1 only on the first record (lowest image_id) for each different advert_id. IE: I would want to update the records with image_id of 1 and 4 and set the main field to 1.

分享到QQ

分享到微博

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

发布评论

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

小红帽 2024-12-01 06:57:50

您无法使用 MySQL 在单个查询中完成此操作。它不允许您在更新表的同时对表进行子选择。但是，如果确实如此，那么查询将是

UPDATE Advert_images
SET (main = 1)
WHERE (image_id IN (
   SELECT min(image_id)
   FROM Advert_images
   GROUP BY advert_id
));

Nothing 表示您无法通过外部脚本分两步完成。运行内部查询来获取“最低”图像的 id，然后运行单独的更新查询来更改主字段值。

You can't do it in a single query with MySQL. It doesn't allow you to update a table while also doing a subselect on the table at the same time. However, if it it did, then the query would've been

UPDATE Advert_images
SET (main = 1)
WHERE (image_id IN (
   SELECT min(image_id)
   FROM Advert_images
   GROUP BY advert_id
));

Nothing says you can't do it in two steps via an external script. Run the inner query to get the ids of the "lowest" images, then run a separate update query to change the main field values.

回复收藏 0 原文

挽清梦 2024-12-01 06:57:50

使用派生表可以让您在 UPDATE 中多次引用一张表：

UPDATE Advert_images a
INNER JOIN (
 SELECT advert_id, MIN(image_id) as image_id FROM Advert_images 
 GROUP BY advert_id)b
ON b.image_id = a.image_id
SET a.main = 1

Using derived tables lets you refer to one table multiple times in UPDATE :

UPDATE Advert_images a
INNER JOIN (
 SELECT advert_id, MIN(image_id) as image_id FROM Advert_images 
 GROUP BY advert_id)b
ON b.image_id = a.image_id
SET a.main = 1

回复收藏 0 原文

~没有更多了~