Update table with info from another table with modifications

Question

I want to move data (with modifications) from one table to another.

UPDATE table2 
SET val_new=(SELECT (table1.val_old - SUM(table3.val_2)) as some1 
FROM table3 
INNER JOIN table1 ON table3.id_some=.table1.id_some 
WHERE table3.id_typ<=2 
AND table3.id_status=1 
group by table3.id_some);

I have error:

Error Code: 1242. Subquery returns more than 1 row

What I must do to correct this query?

[This question](http://stackoverflow.com/questions/4241621/mysql-insert-into-table-data-from-another-table) should help. — Vincent Ramdhanie, Dec 22 '12 at 22:16
No, because I have data in table2 and I must change only column val_new. — user1924202, Dec 22 '12 at 22:17
You want to set the `val_new` column to the same thing in every row of `table2`? — Barmar, Dec 22 '12 at 22:22

Bohemian · Answer 1 · 2012-12-22T22:31:00.957

0

The simplest way to fix is this:

UPDATE table2 SET 
val_new=ifnull((
     SELECT (table1.val_old - SUM(table3.val_2)) as some1 
     FROM table3 
     INNER JOIN table1 ON table3.id_some=.table1.id_some 
     WHERE table3.id_typ<=2 
     AND table3.id_status=1 
     WHERE table3.table2_id = table2.id -- ADDED THIS LINE
     group by table3.id_some), val_new);

edited Dec 22 '12 at 22:31

answered Dec 22 '12 at 22:20

Bohemian

412,405
93
575
722

I added: `AND table3.table2_id = table2.id` and works almost perfect. Now I have warning: `76 row(s) affected, 49 warning(s): 1048 Column 'val_new' cannot be null 1048 Column 'val_new' cannot be null 1048 Column 'val_new' cannot be null 1048 Column 'val_new' (...) ` – user1924202 Dec 22 '12 at 22:27
I've edited the SQL to not update if the value from the sub query returns a null. Try it now – Bohemian Dec 22 '12 at 22:32

Update table with info from another table with modifications

1 Answers1