How to update individual rows in duplicate records

I have two tables t1 and t2

t1 →

id          line        Amt         
----------- ----------- ----------- 
1           1           0
1           2           0
2           1           0
2           2           0
2           3           0
3           3           0
3           4           0
3           5           0
4           2           0
4           3           0

--------------------------

      

t2 →

id          amt         
----------- ----------- 
1           500
2           350
3           750
4           400

      

In this case, I need to update t1 with an amount from t2. But I only need to update one line for each id on the minimum line. I can do this in MSSQL using the following query -

update a set a.amt=c.amt from T1 a inner join (
select id,min(line) line from T1 group by Id) b
on a.id=b.id and a.line=b.line
Inner join T2 c on a.id=c.Id

      

I want to do this in MYSQL. Is there any idea to do something like this

+3


source to share


1 answer


You need to customize the remove syntax from

, move the clause set

after combining the part

update T1 a 
inner join (
  select id,min(line) line from T1 group by Id
) b on a.id=b.id and a.line=b.line
inner join T2 c on a.id=c.Id
set a.amt=c.amt 

      



DEMO

0


source







All Articles