Search code examples
mysqlsql-update

Update multiple columns in one table based on values in another table in mysql

I have a tableA, that holds 40 million records. It has this structure

Id A B C
1 N O 10
1 N O 20
2 B G 15
2 B G 25
3 E U 50
3 E U 60

I need to change the values in column A and B in tableA based on values in tableB (where id is unique). Table B looks like this

Id A B
1 V M
2 Q W
3 E U

Thus ending up with the result for table A

Id A B C
1 V M 10
1 V M 20
2 Q W 15
2 Q W 25
3 E U 50
3 E U 60

Since Id=3 is the same in both table A and B I would rather avoid to write from B to A for those records, thinking that would be more efficient?

I thought along these lines

UPDATE tableA
SET A = (SELECT A
         FROM tableB
         WHERE tabelA.id = tableB.id)

But not sure when I need to update several columns in the same step, and also in terms of only update if data really is different between tableA and tableB.

Solution

  • You can use the multiple-table UPDATE syntax to join the tables:

    UPDATE tableA JOIN tableB USING (Id)
SET    tableA.A = tableB.A,
       tableA.B = tableB.B

    See it on sqlfiddle.