MySQL – SQLyog Job Agent (SJA) – Two Way Database Syncronization Based on Date Time

I am working with MySQL and trying to use SQLyog Job Agent (SJA)

I am trying to synchronize two databases in different physical locations.

There is a date time column in each table, the row with the most current date time should overwrite the older row.

The SJA wizard allows you to enter a WHERE clause, but I am not sure how to word the condition. I would like to write something like this:

WHERE DB1.Table1.dateTimeField > DB2.Table1.dateTimeField THEN update DB2 row with DB1 row as it's newest

But, it needs to work both ways, so

WHERE DB2.Table1.dateTimeField > DB1.Table1.dateTimeField THEN update DB1 row with DB2 row as it's newest

I am sure I could write my own DB sync script in PHP to update each table just based on the newest date time stamp, but I thought I would leverage the SJA tool as I would think it would better performance wise.

I am open to using alternative software.

Thanks.

EDIT

I have full access to both tables and their structure.

Answer

a pure sql based solution would be

SELECT IF(DB1.Table1.dateTimeField > DB2.Table1.dateTimeField
,@sql := "UPDATE DB1.table1 a INNER JOIN DB2.tbale1 b ON a.col1 = b.col2 SET  a.col2 = b.col2,a.col4 = b.col4 WHERE a.col3 = 1"
,@sql := "UPDATE DB2.table1 a INNER JOIN DB1.tbale1 b ON a.col1 = b.col2 SET  a.col2 = b.col2,a.col4 = b.col4 WHERE a.col3 = 1");
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

The SELECT IF must if Course have a FROM clause the get the correct dates

The update statement looks like this, so that the correct row will be copied. This you must test so Wit a SELCT and INNER JOIN so tht only the cirrect row will be updated and to right row is joined.

This also onöly works if you know which column belong to which

UPDATE DB1.tabl1 a INNER JOIN DB2.tbal1 b ON a.col1 = b.col2 
SET
   a.col2 = b.col2,a.col4 = b.col4 WHERE a.col3 = 1

If you have tested all sql stetemenst you can run this in an event so that it will rum every 5 Minutes or so.