CODEWITHSUNDEEP
mysqlsql-update
Leto
Leto

Reputation: 2674

update only rows with max(id) with condition

I can't find a solution for my problem..

Let's say we have a simple table like this :

For the moment, the flag column is set to 0.

I want to update this table and set the flag to 1 only for rows that have the greatest (newest)id when grouped by group_id.

I have been testing a lot of things, without success.

Example: 

id: 1, group_id: 1, flag: 0
id: 2, group_id: 1, flag: 0
id: 3, group_id: 1, flag: 0
id: 4, group_id: 2, flag: 0
id: 5, group_id: 2, flag: 0

Will become :

id: 1, group_id: 1, flag: 0
id: 2, group_id: 1, flag: 0
id: 3, group_id: 1, flag: 1
id: 4, group_id: 2, flag: 0
id: 5, group_id: 2, flag: 1

Upvotes: 0

Views: 1521

Answers (2)

Regenschein
Regenschein

Reputation: 1502

For reference, this is a TSQL (SQL Server) Solution to that question.

UPDATE tablename
SET flag = 1
FROM (
   SELECT max(id) As maxid FROM tablename GROUP BY group_id
) A
WHERE 
  id = A.maxid

or even 

UPDATE tablename
SET flag = 1 
WHERE id IN ( SELECT max(id) FROM tablename GROUP BY group_id )

Upvotes: 0

fthiella
fthiella

Reputation: 49089

UPDATE
  yourtable t1 INNER JOIN (SELECT group_id, MAX(ID) mx_id
                           FROM yourtable
                           GROUP BY group_id) t2
  ON t1.group_id = t2.group_id AND t1.id = t2.mx_id
SET
  t1.flag = 1

Please see fiddle here.

Upvotes: 1

Related Questions