MySQL sync-update if not protected

If you have a MySQL query that should sync-update between two tables with the condition that the field your about to update isn´t protected you can use the following query:

REPLACE INTO table1
SELECT * FROM table2 t2
WHERE NOT EXISTS (SELECT * FROM table1 t1 WHERE table1.fieldId = table2.fieldId AND field_protected = 1)

In other words:

REPLACE INTO table1

This line says, ”If an old post exist, delete it and insert this new post (if statements below is fufilled)”.

SELECT * FROM table2 t2

This line says, ”Select all fields from table2, with alias t2”

WHERE NOT EXISTS (SELECT * FROM table1 t1 WHERE table1.fieldId = table2.fieldId AND field_protected = 1)

This line says, ”only select a post from table2 (row above) if the field, field_protected, in the post from table1 hasn´t the value 1”.

or

”only select posts in table2 if there are no hits in the query ”SELECT * FROM table1 t1 WHERE  table1.fieldId = table2.fieldId AND field_protected = 1”

Annonser

Kommentera

Fyll i dina uppgifter nedan eller klicka på en ikon för att logga in:

WordPress.com Logo

Du kommenterar med ditt WordPress.com-konto. Logga ut / Ändra )

Twitter-bild

Du kommenterar med ditt Twitter-konto. Logga ut / Ändra )

Facebook-foto

Du kommenterar med ditt Facebook-konto. Logga ut / Ändra )

Google+ photo

Du kommenterar med ditt Google+-konto. Logga ut / Ändra )

Ansluter till %s