CODEWITHSUNDEEP
mysql
Bob
Bob

Reputation: 1211

MySQL - Update last occurrence of string

I have a table like this:

 +----------------------------+
 | id    | name     |  helper |
 +----------------------------+
 |    1  | user1    |    NULL |
 |    2  | user1    |    NULL |
 |    3  | user1    |    NULL |
 |    4  | user2    |    NULL |
 |    5  | user2    |    NULL |
 +----------------------------+

Now I want to update the LAST occurrence of "user1" - how do I do that?

This is my example query right now:

    UPDATE Table SET helper = 'bob' WHERE name = 'user1' AND helper IS NULL;

However, this updates all the "user1" Entries.

Thanks in advance.

Upvotes: 1

Views: 406

Answers (2)

fancyPants
fancyPants

Reputation: 51918

update t set helper = 'bob' where name = 'user1' order by id desc limit 1;

Upvotes: 3

ForguesR
ForguesR

Reputation: 3618

This should work.

UPDATE Table SET helper = 'bob' WHERE id = (SELECT MAX(id) FROM Table WHERE name = 'user1')

Just use MAX(id) to get the last row of 'user1'.

Upvotes: 2

Related Questions