CODEWITHSUNDEEP
mysqlsql-update
Alexander
Alexander

Reputation: 644

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.

Upvotes: 3

Views: 18388

Answers (3)

pankaj
pankaj

Reputation: 1914

Use this query for this but you have to put your where condition as you want.

UPDATE tabe2
SET subject_id = (
        SELECT GROUP_CONCAT(sub_id SEPARATOR ', ') AS sub_id
        FROM tabe1
        )

Upvotes: 0

user3393753
user3393753

Reputation: 86

Update A a
inner join B b on a.id = b.id
set a.A = b.A, 
    a.B = b.B
WHERE a.A <> b.A and a.B <> b.B

Upvotes: 0

eggyal
eggyal

Reputation: 126055

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.

Upvotes: 9

Related Questions