Updating every first event
I have these two 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 , quantity ) 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 post of ONLY each id from the test table with the sum (sum) from the second table based on the id.
So far I have this, but it updates all ocurrences, not 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 only updates the first row of the first ID. I need it to update the first line of each id (first line of 1, first line of 2, first line of 3)
source to share
This assumes that your table test
does not have multiple id
ands suma
, and if you do, it is not a problem that both of them are being updated.
He also assumes that "first" is the one with the lowest suma
. There is no order in a relational table unless you specify it with a clause order by
.
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;
source to share
Not tested
Try this code, it will update every test line where id contains only the minimum suma value. Means. It will only update the first column of every other 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)))
source to share