CODEWITHSUNDEEP
mysqlsqldatabasecopy-paste
Mild Fuzz
Mild Fuzz

Reputation: 30691

Copy one column from one database to another

I need to copy the content from a column in one database into the matching column in another, so that the same content goes in the record with the same ID. Something like the following pseudo stuff:

SET database2.table1.columnA TO database1.table1.columnA WHERE database2.id = database1.id

Upvotes: 11

Views: 14009

Answers (4)

Meetai.com
Meetai.com

Reputation: 6928

Here's what works when your columns are not identical.

INSERT INTO `new_database`.`new_table`(`column1`,`column2`,`column3`)
SELECT `old_table`.`column2`, `old_table`.`column7`, `old_table`.`column5` 
FROM `old_database`.`old_table`

Updated from https://stackoverflow.com/users/3746968/elgoots - Unable to properly paste this in a comment

Upvotes: 0

Elgoots
Elgoots

Reputation: 158

if not identical columns for other people you can use the below:

USE `old_database`;
INSERT INTO `new_database`.`new_table`(`column1`,`column2`,`column3`)
SELECT `old_table`.`column2`, `old_table`.`column7`, `old_table`.`column5` 
FROM `old_table`

Upvotes: 4

slaakso
slaakso

Reputation: 9070

MySQL uses syntax:

update database1.table1, database2.table1
set database1.table1.columnA = database2.table1.columnA
where database1.table1.id = database2.table1.id;

Upvotes: 19

Mark Brackett
Mark Brackett

Reputation: 85655

You can use JOIN in an UPDATE statement:

UPDATE table1 t1 
JOIN database1.table1 as t2 ON
   t1.id = t2.id
SET
   t1.columnA = t2.columnA

Upvotes: 6

Related Questions