CODEWITHSUNDEEP
mysqlsqldatabasemerge
Deneme Deneme As
Deneme Deneme As

Reputation: 1

Matching two databases and insert same rows to another

I have 2 databases named DB1 and DB2 with a table called movies. movie contains movie_name, year, synopsis, AKA etc. As you can see synopsis and AKA columns are empty. I want to match two columns (movie_name and year) and insert identical results into DB1's empty columns.

DB1.movies

movie_name | year | synopsis | AKA
----------------------------------------
titanic    | 1997 |          |
matrix     | 1999 |          |
alien      | 1979 |          |

DB2.movies

movie_name | year | synopsis | AKA
----------------------------------------
titanic    | 1997 | xxxxxxx  | XXX
matrix     | 1999 | yyyyyyy  | YYY
alien      | 1979 | zzzzzzz  | ZZZ

After then, DB1.movies should look like this:

DB1.movies

movie_name | year | synopsis | AKA
----------------------------------------
titanic    | 1997 | xxxxxxx  | XXX
matrix     | 1999 | yyyyyyy  | YYY
alien      | 1979 | zzzzzzz  | ZZZ

What can I do? Thanks!

Upvotes: 0

Views: 95

Answers (2)

Mike Brant
Mike Brant

Reputation: 71424

This should do the trick:

UPDATE db1.table1 JOIN db2.table2 on db1.table1.movie_name = db2.table2.movie_name AND db1.table1.year = db2.table2.year SET db1.table1.synopsis = db2.table2.synopsis, db1.table2.AKA = db2.table2.AKA

Note I didn't know the table names for the tables in db1 and db2, so I just called them table1 and table2 respectively.

Upvotes: 1

Gingi
Gingi

Reputation: 2304

(I'm going to assume that the tables are called movies in each database.)

If you want to replace the data with movies in DB2 (and the tables in both databases have matching primary keys), then you can do the following:

replace into db1.movies select * from db2.movies

If you just want to update the synonpsis and AKA fields within existing rows, do:

update db1.movies mov1, db2.movies mov2
   set mov1.synopsis   = mov2.synopsis,
       mov1.AKA        = mov2.AKA
 where mov1.movie_name = mov2.movie_name

Upvotes: 1

Related Questions