CODEWITHSUNDEEP
mysql
Jeff
Jeff

Reputation: 1017

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

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

Upvotes: 0

Views: 1027

Answers (2)

Shadi Ajam
Shadi Ajam

Reputation: 201

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.

Upvotes: 0

mysqlrockstar
mysqlrockstar

Reputation: 2612

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;

Upvotes: 1

Related Questions