Łukasz Łukasz - 6 months ago 21
MySQL Question

Mysql, update query

I have table like this:

table1

first_id second_id value
1 0 10
2 0 60 <- can be bad value, need update this
2 12 30
2 14 30
3 0 50
4 0 100 <- can be bad value, need update this
4 20 50
4 41 30
4 33 20


I need update rows that have second_id = 0 and in table exists rows with same first_id but second_id != 0. I need update this rows with sum of rows that have same first_id and second_id != 0.

For example:

first_id = 3 and second_id = 0 => not update, 0 rows with first_id = 3 and second_id != 0

first_id = 4 and second_id = 0 => update, SUM(50,30,20) = rows with same first_id and second_id != 0


How can I do this in one update statement?

I tried sth like this but without effect (problem with recursive query?).

UPDATE table1 t1 SET t1.value =
(
SELECT SUM(t2.value)
FROM table1 t2
WHERE t2.second_id != 0 AND t2.first_id = t1.first_id
)
WHERE t1.second_id = 0 AND
(
SELECT COUNT(*)
FROM table1 t3
WHERE t3.first_id = t1.first_id
) > 1

Answer

MySQL doesn't let you update the same table that you're running a SELECT statement on. So you have to do a subquery and then alias the result as a temporary table, and JOIN on that.

UPDATE table1 tbl1
    JOIN (SELECT t2.first_id, SUM(t2.the_value) as theValue FROM table1 t2 WHERE t2.second_id != 0 group by t2.first_id) tbl2 ON tbl2.first_id = tbl1.first_id
SET tbl1.the_value = tbl2.theValue
WHERE tbl1.second_id = 0;

DEMO

In the demo, I initialized the value columns of your marked to 1 so you can see they get updated to the desired value.