Only update changed values for ON DUPLICATE KEY UPDATE

Question

I have a list of checkboxes that are assigned either a value of 1 or 0 that all get passed in the $_POST variable.

My current sql statement inserts all of them correctly but when I change a value and run the sql statement again, it updates my "updated_at" column for all the items.

Is there a way to only update changed values so it doesn't update the "updated_at" column for every entry? id is my primary key and site_id is unique.

SQL Statement:

        $sql = "INSERT INTO admin_sites2 (site_id,created_at,active)
                VALUES ('$key',now(),'$value')
                ON DUPLICATE KEY
                UPDATE active = $value,
                updated_at = now()
               ";

Answer 1

I recommend you use the special VALUES() function to reference the value that would have been inserted into a column (if the insert had succeeded).

You can use an expression that compares the current value of the column to the newly provided value, and conditionally return either the current value of the column, or a new expression.

For example:

            INSERT INTO admin_sites2 (site_id,created_at,active)
            VALUES ('$key',now(),'$value')
            ON DUPLICATE KEY
            UPDATE updated_at = IF(active=VALUES(active),updated_at,NOW()),
            active = VALUES(active)

The expression IF(a,b,c) works like this: it evaluates a as a boolean; IF a is TRUE then return b else return c.

In the example above, if the current value of the active column is the same as the new value being inserted/assigned, the IF() expression returns the current value of the updated_at column. (That means that the value of the updated_at column won't be changed, since it's assigning the value that's already stored.) Otherwise, the value of NOW() will be assigned to the updated_at column.

I believe it's important to do the check of the current value of the active column BEFORE the assignment is made to the active column. (Note that the assignment to updated_at appears BEFORE the assignment to active.)

This is because of the way MySQL processes values in SQL. MySQL doesn't maintain a consistent snapshot view of the row ('at the start of the statement'). After a new value is assigned to a column, later references to that column will return the newly assigned value rather than the value it was before the statement started making changes to the row.