Sometimes you wanted to update columns of one MySQL table by quering another MySQL table and getting a column value from the later table. For example you have a ‘students’ table and you wanted to create a new field ‘totalMarks’ in it and wanted to fill or update by taking sum of the ‘marks’ field from another table named ‘marks’. Here comes the use of subquery to update column.
To update a single column of a MySQL database table is quite straight forward. One would do it like this:
[sql]UPDATE table_a SET column_a1 = (SELECT
column_b1 FROM table_b
WHERE table_b.column_b3 = table_a.column_a3);[/sql]
Updating two columns with a subquery in MySQL
Now lets assume that we need to update two or more columns in MySQL. Unlike SQL it WON’T WORK in MySQL:
[sql]UPDATE table_a SET (column_a1, column_a2) = (SELECT
column_b1, column_b2 FROM table_b
WHERE table_b.column_b3 = table_a.column_a3);[/sql]
But, this Works:
[sql]UPDATE table_a JOIN table_b ON table_a.column_a3 = table_b.column_b3
SET table_a.column_a1 = table_b.column_b1,
table_a.column_a2 = table_b.column_b2;[/sql]
And this works really well as well:
[sql]UPDATE table_a a
INNER JOIN
(
SELECT field1,
COUNT(*) rowsCount,
SUM(field2) fieldSum
FROM table_b b
GROUP BY field1
) b ON b.field1 = a.field1
SET a.rowsCount = b.rowsCount
a.field2 = b.fieldSum[/sql]
