英文:
Return & UPDATE mysql rows with different user creator
问题
UPDATE 语句的语法不正确，以下是正确的更新语句：
UPDATE posts
SET status = 0
WHERE id_user IN (343, 345, 356)
ORDER BY date ASC
LIMIT 3;
这个更新语句将会更新每个用户（id_user 为 343、345、356）的前三条记录，根据日期升序排列。
英文:
Ok, I have the next table "posts"
id_user id_post post date status
343 34534 Hi 2023-01-01 01:00:00 1
343 232324 Hello 2023-01-01 02:00:00 1
343 32423 How are you? 2023-01-01 03:00:00 1
345 345454 Whats up? 2023-01-01 04:00:00 1
345 3457543 Was geth ab? 2023-01-01 05:00:00 1
356 454354 Wie geth's? 2023-01-01 06:00:00 1
Ok I want to update the first row of each user (343,345 & 356) based on the date
UPDATE posts SET status = 0 WHERE 1 ORDER BY date ASC LIMIT 3
Expected result
Hi 2023-01-01 01:00:00
Whats up? 2023-01-01 04:00:00
Wie geth's? 2023-01-01 06:00:00
I tried
UPDATE posts p
WHERE 1
LEFT JOIN post c ON p.id_user !=c.id_user
ORDER BY date ASC LIMIT 3
But it doesnt work, what am I doing wrong?
答案1
得分: -2
你可以以类似的方式来执行，就像你选择每个组中的第一/最后一行一样：
UPDATE posts AS p1
JOIN (
SELECT id_user, MIN(date) AS mindate
FROM posts
GROUP BY id_user
) AS p2 ON p1.id_user = p2.id_user AND p1.date = p2.mindate
SET p1.status = 0
需要执行一个单独的查询来返回它们，因为MySQL没有提供直接返回已更新行的方法。
SELECT *
FROM posts
WHERE status = 0
英文:
You do it similarly to the way you select the first/last row in a each group
UPDATE posts AS p1
JOIN (
SELECT id_user, MIN(date) AS mindate
FROM posts
GROUP BY id_user
) AS p2 ON p1.id_user = p2.id_user AND p1.date = p2.mindate
SET p1.status = 0
You'll need to do a separate query to return them, because MySQL doesn't provide a way to return the rows that were updated directly.
SELECT *
FROM posts
WHERE status = 0
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论