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