How to update table1 with content from table2 with php and mysql

Question

I have two tables in my database, table1 and table2. They are identical. But sometimes i change the data in table1.

How do i copy the data from table1 and update table2 to look the same?

I tried "REPLACE INTO table2 SELECT * FROM table1" but it works like INSERT and just make new rows instead of updating the existing ones.

Answer 1

For REPLACE INTO to work as intended, the destination table must have a primary key defined, otherwise MySQL cannot determine whether a row already exists and always assumes a new row. As a result, for tables without a primary key, REPLACE INTO acts exactly like INSERT INTO.

Alternatively, you can use two queries, one UPDATE and one INSERT, with appropriate WHERE (NOT) EXISTS clauses. The advantage of this is that it's portable (REPLACE INTO is a MySQL extension).

Answer 2

Another alternative is to run two commands...

truncate table table2;
insert into table2 select * from table1;