Search code examples
mysqlsqlinsert-updatecreate-tableinsert-into

How to update all fields from one table to another table in the same database using mySQL?

There are 2 tables in the same database with the same structure. I want to copy all data from one table to the other table using mySQL. The source table may have the same, less or more number of rows of the destination table.

I tried searching. I found 2 approaches:

Approach #1

TRUNCATE destination;
INSERT INTO destination SELECT * FROM source

Approach #2

DROP TABLE destination;
CREATE TABLE destination SELECT * FROM source

Isn't there any other approach involving UPDATE?

Solution

  • Update I don't think so.

    You can do Insert

    Insert into destination
(
column_1,
column_2,
....
)
SELECT 
column_1,
column_2,
....
FROM source

    Note: No. of columns mention in destination = No. of columns mention in source

    By the approach #1 will not work always.

    and approach #2 will always work