Mysql, update query

Question

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

possible duplicate of [Mysql error 1093 - Can't specify target table for update in FROM clause](http://stackoverflow.com/questions/45494/mysql-error-1093-cant-specify-target-table-for-update-in-from-clause) — Tony Hopkinson, May 29 '14 at 21:05

score 1 · Answer 1 · edited May 23 '17 at 11:44

1

I learned that you cannot update based on the immediate sub-query. That's why you see nested sub-queries in the following query.

UPDATE table1 t1 
    INNER JOIN table1 t2
    ON t1.first_id = t2.first_id and t2.second_id != 0
    SET t1.value = (SELECT total_value from (SELECT first_id, SUM(value) total_value FROM table1 WHERE second_id != 0 GROUP BY first_id) as t2 WHERE t2.first_id = t1.first_id)
    WHERE t1.second_id = 0;

References:

SQL Fiddle Demo

edited May 23 '17 at 11:44

Community

1
1

answered May 29 '14 at 20:46

Joseph B

5,519
1
15
19

It's not necessary because i'm checking > 1 not >= 1. Problem is that my query gives me error: "You can't specify target table 't1' for update in FROM clause" – Łukasz May 29 '14 at 20:56
It seems that you would have to do a join. Check this question out - http://stackoverflow.com/questions/45494/mysql-error-1093-cant-specify-target-table-for-update-in-from-clause – Joseph B May 29 '14 at 21:05

score 1 · Accepted Answer · answered May 29 '14 at 21:12

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.

Mysql, update query

2 Answers2