INSERT INTO with ON DUPLICATE KEY

Question

I have a little dilemma with my query. In order for my code to be optimal I would like to do as little logic in PHP as possible and let MySQL take care of most the stuff.

So this is what I have: I have an array with e.g. 10 keys. Each key withholds an new array some values. Each value represents a row in the database table.

Lets say my database table has 6 columns. the first one is a primary with auto increment. Column 2, 3 and 4 should be unique. Column 5 is a TEXT representation which should not be unique. Column 6 is a INT(1) what either is 0 or 1.

Now, when I insert data into my table I use INSERT INTO mytable (col2, col3, col4, col5, col6) VALUES ('$col2','$col3','$col4','$col5',1) ON DUPLICATE KEY UPDATE col5 = '$col5', col6 = 0; This works perfect, but it does not accomplish what I need.

When I send in a the same array once again, but for key3, col5 row I change the data to something else, I only want col6 for that key to be updated to 0; the rest should stay 1. What happens now is that every row will get updated, even though it is updated with the exact same data.

So, what I need is I only want to update col6 if col5 is different from the current saved value. How can I do that?

Answer 1

using IF

    INSERT INTO mytable (col2, col3, col4, col5, col6) VALUES
 ('$col2','$col3','$col4','$col5',1)
 ON DUPLICATE KEY UPDATE col5 = '$col5', col6 = IF(col5 != '$col5', 0, col6);