CODEWITHSUNDEEP
mysqlsql-update
themerlinproject
themerlinproject

Reputation: 3582

mysql - updating rows between tables

I have two tables with identical structure.

old_table looks something (example, not actual table) like this:

Name  -  DOB  - id
John  -  xxxx - 344

new_table looks like this:

Name  - DOB  - id
John  - 1980 - 344

Where the new_table has the DOB column filled in. The ID field (unique) and the rest of the structure is the same between tables. I want to update the DOB fields in the old_table with the values from the new_table where the ID fields are the same (so in the above example where the 'id'=344, etc, for all rows and ids).

I was thinking of using: INSERT INTO old_table (DOB) SELECT DOB FROM new_table WHERE...

but then my mysql knowledge trails off. Should I even be using INSERT or can I use UPDATE here? And how do I only pull the DOB value from the old_table where the ID field = the ID field of the new_table?

Thanks..

Upvotes: 1

Views: 1211

Answers (2)

Joe Stefanelli
Joe Stefanelli

Reputation: 135818

UPDATE old_table, new_table
    SET old_table.DOB = new_table.DOB
    WHERE old_table.id = new_table.id

EDIT: Based on comment from OP

UPDATE old_table, new_table
    SET old_table.DOB = new_table.DOB
    WHERE old_table.id = new_table.id
        AND old_table.DOB = 'xxxx'
        AND old_table.field4 = '-'

Upvotes: 4

Ratinho
Ratinho

Reputation: 298

use update UPDATE old table ot set DOB=(select DOB from newTable nt where nt.id=ot.id) something like that should work

Upvotes: 0

Related Questions