CODEWITHSUNDEEP
mysqlsql-update
ViccroyHD
ViccroyHD

Reputation: 49

Update Each First Occurrence

I have these 2 tables:

Test (id, suma) with the following values:
1,1
1,2
2,3
2,4
2,5
3,6
3,7
3,8
3,9

Payments (id, amount) with the following values:
1, 5
1, 7
1, 11
2, 14
2, 3
3, 4
3, 2



What I need to do is update the first ocurrence ONLY of each id from the test table with the sum(amount) from the second table based on the id.

So far I have this, but it updates all ocurrences instead of just the first one for each id:

UPDATE test SET test.suma = (SELECT SUM(amount) FROM payments WHERE payments.id = test.id)


If I add LIMIT 1, it will only update the first row of the first id. I need it to update the first row of each id (first row of 1, first row of 2, first row of 3)

Upvotes: 4

Views: 1412

Answers (2)

CodeSlayer
CodeSlayer

Reputation: 1327

Not tested

Try this code, it will update every row of test where id contains the minimun value of suma only. Means. It will update only the first column of every different id

 UPDATE test SET test.suma = (SELECT SUM(amount) FROM payments 

 WHERE payments.id = (select test.id from test where suma = (select min(suma) from test)))

Upvotes: 2

fancyPants
fancyPants

Reputation: 51918

This one assumes you have no multiple id and suma in your table test, and that if you do, it's no problem that both are updated.

Also it assumes that the "first" one is the one with lowest suma. There is no order in a relational table, unless you specify one with order by clause.

UPDATE test t
INNER JOIN (
    SELECT id, SUM(amount) amount FROM payments GROUP BY id
) p ON p.id = t.id
INNER JOIN (
    SELECT id, MIN(suma) suma FROM test GROUP BY id
) st ON t.id = st.id AND t.suma = st.suma
SET t.suma = p.amount;

Upvotes: 1

Related Questions