MySQL query to update all columns in one table from table in different database

Question

I need to update all data from one table to an identical table (layout wise) in another database. Is this possible? so far I have tried the below (first name only although ideally I want all columns without listing them individually.)

The data I am trying to fix only effect user ids between 50 and 266

This gives me a syntax error

UPDATE
   foundation-restore.archive,    foundation.archive   
SET
   foundation-restore.archive.FName = foundation.archive.FName  
WHERE
   foundation-restore.archive.user_id = foundation.archive.user_id    
   AND    foundation-restore.archive.user_id > 50    
   AND    foundation-restore.archive.user_id < 266

Note - data is on a same layout table on a different db

possible duplicate of [MySQL update table based on another tables value](http://stackoverflow.com/questions/12394506/mysql-update-table-based-on-another-tables-value) — Joel Hinz, Jun 01 '15 at 16:43
possible duplicate of [SQL: Update table where column = Multiple Values](http://stackoverflow.com/questions/5826456/sql-update-table-where-column-multiple-values) — rogerdeuce, Jun 01 '15 at 16:44
It is a bit unclear from your question, but if the database tables contain the exact same data you can do a mysqldump of the table and then restore it into the second database. — kojow7, Jun 01 '15 at 16:54
I have about 200 records that are screwed and I need to fix them from a back up database. If I try and import them back in I get a duplicate key error so thinking I can update them to keep their user id's — Jeff, Jun 01 '15 at 17:03
I have put the query together from a similar one on stack. The error is: #1064 - You have an error in your SQL syntax; — Jeff, Jun 01 '15 at 17:04

mysqlrockstar · Answer 1 · 2015-06-01T17:13:17.780

1

Try this ( will work in tables of same database, most probably will not work on different database)

UPDATE
   foundation-restore.archive INNER JOIN  foundation.archive
ON  foundation-restore.archive.user_id = foundation.archive.user_id 
SET
   foundation-restore.archive.FName = foundation.archive.FName  
WHERE foundation-restore.archive.user_id BETWEEN  50  AND 266;

edited Jun 01 '15 at 17:13

answered Jun 01 '15 at 17:04

mysqlrockstar

2,536
1
19
36

is it possible to update all fields without writing them out, eg SET foundation-restore.archive.* – Jeff Jun 01 '15 at 17:06
I think its not possible. We need to specify column names. But that is a different issue, Have you checked if the above query works ? – mysqlrockstar Jun 01 '15 at 17:08

score 0 · Answer 2 · answered Jun 01 '15 at 20:11

You want to copy all columns data from alternate database table to other database table in different databases.

To do this you have to do many tricks.

This is final SQL

SET @Source_Database = "your-source-database";
SET @Dest_Database = "your-destination-database";
SET @Table = "your-table";
SET @Key_Field = "key-field-of-table";
SET @SetStr = (
SELECT 
  GROUP_CONCAT(CONCAT(@Dest_Database,".",@Table,".",COLUMN_NAME," = ",@Source_Database,".",@Table,".",COLUMN_NAME))
FROM INFORMATION_SCHEMA.COLUMNS 
WHERE TABLE_SCHEMA=@Source_Database 
  AND TABLE_NAME=@Table
  AND COLUMN_KEY<>"PRI");


set @SQLStr = CONCAT(
  "update ",@Dest_Database,".",@Table,
  " inner join ",@Source_Database,".",@Table,
  " on ",@Dest_Database,".",@Table,".",@Key_Field,
  " = ",@Source_Database,".",@Table,".",@Key_Field,
  " set ",@SetStr,
  " where ",@Dest_Database,".",@Table,".",@Key_Field ," < 10"
  );

PREPARE SQL1 FROM @SQLStr;

EXECUTE SQL1;

Notice

1st : we need to know all fields names and put on set clause, so you we use INFORMATION_SCHEMA table

SELECT 
  COLUMN_NAME
FROM INFORMATION_SCHEMA.COLUMNS 
WHERE TABLE_SCHEMA=@Source_Database 
  AND TABLE_NAME=@Table
  AND COLUMN_KEY<>"PRI"

2nd : We use group_concat function to get all fields in single line

GROUP_CONCAT(COLUMN_NAME)

and add concat function to create a scheme of SET Clause

set @SetStr = (
SELECT 
  GROUP_CONCAT(CONCAT(@Dest_Database,".",@Table,".",COLUMN_NAME," = ",@Source_Database,".",@Table,".",COLUMN_NAME))
FROM INFORMATION_SCHEMA.COLUMNS 
WHERE TABLE_SCHEMA=@Source_Database 
  AND TABLE_NAME=@Table
  AND COLUMN_KEY<>"PRI");

3rd : we use PREPARE and EXECUTE statements to declare an SQL String and execute it.

PREPARE SQL1 FROM @SQLStr;

EXECUTE SQL1;

Best regards.

MySQL query to update all columns in one table from table in different database

2 Answers2