mysql UPDATE statement updates columns one by one?

user_8372336 0
Viewed 207

i was writing a query in mysql 8 where on duplicate key on the insert because of a unique index it updates the affected row only under a certain condition. my condition is if the started_on column value has changed or not. So i wrote this query

INSERT INTO timers (
            started_on,
            end_on,
            message,
            first_alert_before_end_time,
            alerts_interval,
            referred_flow_instance_id,
            referred_page_id,
            referred_json_timer_id,
            started_by_user_id
        ) VALUES (
            '2025-01-06',
            DATE_ADD('2025-01-06', INTERVAL 5184000 SECOND),
            'Sta scadendo il fascicolo',
            2592000,
            86400,
            1413,
            14,
            1,
            79
        )
        ON DUPLICATE KEY 
        UPDATE
            started_on = IF( DATE(started_on) = DATE('2025-01-01'), started_on, '2025-01-06'),
            end_on = IF( DATE(started_on) = DATE('2025-01-01'), end_on,  DATE_ADD('2025-01-06', INTERVAL 5184000 SECOND)),
            started_by_user_id = IF( DATE(started_on) = DATE('2025-01-01'), started_by_user_id, 79),
            deleted_by_user_id = IF( DATE(started_on) = DATE('2025-01-01'), deleted_by_user_id, NULL),
            reminded_later_by_user_id = IF( DATE(started_on) = DATE('2025-01-01'), reminded_later_by_user_id, NULL),
            deleted = IF( DATE(started_on) = DATE('2025-01-01'), deleted, 0)
    ;

i won't analyze the insert that works, but only the update on duplicate key part.

I noticed an unexpected result: when the IF condition in the updates was true, only started_on was updated but no the other columns that have the very same condition.

So my intuition was that the first row of my update query (started_on = IF( DATE(started_on) = DATE('2025-01-01'), started_on, '2025-01-06'), updates suddenly the column started_on, so that the following conditions result as false because the value changed. What i tried was to put the part that update started_on at the end of all the columns in update, and now they all get updated.

i would like to be sure and find a confirmation if my intuition was right, but after searching a bit on the web i found nothing.

EDIT: it seems tha running the query that updates started_on on top via mysql workbench it works even if on top, but i needed to run it in node using xdevapi library and it works only if placed at the bottom.

mysql-8.0 sql-update mysql sql
2 Answers

Your intuition is correct.
This behavior is documented in 13.2.13 UPDATE Statement with an example:

The second assignment in the following statement sets col2 to the current (updated) col1 value, not the original col1 value. The result is that col1 and col2 have the same value. This behavior differs from standard SQL.

UPDATE t1 SET col1 = col1 + 1, col2 = col1;

Single-table UPDATE assignments are generally evaluated from left to right.

user_104988280

In mysql 5.6, and all versions of MariaDB, any reference to a column will get the original value of that column.

For mysql 5.7 and mysql 8.0, it is more complicated:

In a single-table update, updates effectively happen left to right, with each column update getting the values for columns as if all preceding updates had been applied.

In a multi-table update, it appears that any updates to the primary table happen first, left to right, as in a single-table update. But updates to columns in joined tables seem to always get any values as of when the primary table update was complete, so any changes to primary table columns will be in effect, even if those primary table columns are only set later in the statement, and for references to joined table columns, the original value is used, even if those joined table columns are changed earlier in the statement.

fiddle

ysth_173890
Related