mysql-logo

Updating two columns with a subquery in MySQL

mysql-logo

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:

UPDATE table_a SET column_a1 = (SELECT
column_b1 FROM table_b
WHERE table_b.column_b3 = table_a.column_a3);

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:

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);

But, this Works:

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;

And this works really well as well:

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

Leave a Reply

Your email address will not be published. Required fields are marked *