CODEWITHSUNDEEP
mysql
Peter
Peter

Reputation: 13

How to update table with ORDER BY in the same table?

UPDATE post
SET body = 'hello'
WHERE post_id in (
  SELECT post_id from post WHERE user_id = '79' ORDER BY date DESC
);

I need to update some column in this table by select post_id from the same table but I can't why? And how can I solve this problem? Please help me, thank you.

post_id | user_id | body  | picture | date
1       | 79      | test  | null    | 2015-08-15 09:19:15
2       | 79      | test2 | null    | 2015-08-15 10:10:18

same like this

Upvotes: 1

Views: 45

Answers (2)

fthiella
fthiella

Reputation: 49049

I think you only want to update the last post of user_id = 79.

UPDATE
  post
SET
  body = 'hello'
WHERE
  user_id=79
ORDER BY date DESC
LIMIT 1

Please see a fiddle here.

Upvotes: 0

Elon Than
Elon Than

Reputation: 9765

That query makes absolutely no sense. You don't have to use subquery here, just update user posts by user_id field.

UPDATE post SET body = 'hello'
    WHERE user_id = 79;

Upvotes: 3

Related Questions