Compare and Update two tables based on "last_update"

jplaguna

i am trying to create a MySQL query, and am wondering if anyone could help me get started....

SCENARIO>>

I have one database with two, identical structures:

table1

table1.id
table1.content
table1.last_update

table2

table2.id
table2.content
table2.last_update

i want to synchronize these two tables together, depending on the last_update field.

so... i want to compare all matches of table1.id to table2.id AND

if table1.last_update has a NEWER date, i want the table1.content field to REPLACE/UPDATE the table2.content field

AND

if table2.last_update has a NEWER date, i want the table2.content field to REPLACE/UPDATE the table1.content field

might you have any suggestions? do i need two different queries for this? or any subqueries?

thanks in advance!

🙂 jp

sarahk

howzabout

update table1 set id = table2.id, content = table2.content, last_update = table2.last_update where table2.lastupdate > table1.last_update

and then do the reverse.

I'd look very carefully at the performance hit of this.

wouldn't it be much easier to just update both at the same time? Or do you have different processes hitting the tables?

jplaguna

thanks... i will try that... performance it not a big concern, as this "synchronization" will only be done once or twice a day, and most likely at night...

if anyone knows a way to do the update both ways, at the same time, that would be even better!

there will only be a few read processes running at any given time.

thanks.

jplaguna

i tried the following:

update table1 set id = table2.id, content = table2.content, last_update = table2.last_update where table2.lastupdate > table1.last_update

but i am getting this error:

MySQL said:

Unknown table 'table2' in where clause

Note: Both table1 and table2 are in the same database, so i don't know why it says "unknown.

I am using MySQL 3.23.37.

TTT

http://www.php.net/manual/en/function.date.php

just fecth both in array and calculate date diff

jplaguna

thank you both for your help... i have finally figured it out, and it works great!

🙂 jp