Return & UPDATE mysql rows with different user creator

Question

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?

*it doesn't work* is [not useful](https://idownvotedbecau.se/itsnotworking/). Presumably you get an error as that's not even valid sql. — Stu, Jul 10 '23 at 15:50
If you aren't using MySQL Workbench, you need to download and try it out, it helps you format your query before executing it. d/l here --> https://dev.mysql.com/downloads/workbench/ — easleyfixed, Jul 10 '23 at 15:51
an UPDATE has no result, as such and wouldn't give a result set, so i can notnunderstand what you want — nbk, Jul 10 '23 at 15:51
@nbk instead of being literal, you could propose a good solution :) — joe, Jul 10 '23 at 15:56
@joe instead of asking for a solution, you should act on the requests for clarifications... — Shadow, Jul 10 '23 at 16:13

score -2 · Accepted Answer · answered Jul 10 '23 at 16:19

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

Return & UPDATE mysql rows with different user creator

1 Answers1