UPDATE priority order for updating multiple tables

Question

If I have an UPDATE statement, where am I doing SET p.old_email = u.email, u.email = NULL, will always be p.old_email = u.emailup to u.email = NULL? The reason I ask, I do not observe this behavior.

mysql> SHOW TRIGGERS;
UPDATE
Empty set (0.01 sec)

mysql>
mysql> UPDATE
    -> users u INNER JOIN people p ON p.id = u.id INNER JOIN entities e ON e.id = p.id
    -> SET p.old_username = NULL, p.username = 'myusername',
    -> p.old_email = NULL, u.email = '[e-mail]',
    -> e.record_status = 'inactive', e.date_modified = NOW( ), e.modified_by_id =506836355
    -> WHERE u.id =1753671666 AND u.id !=506836355 AND e.sites_id =2846702735;
Query OK, 3 rows affected (0.00 sec)
Rows matched: 3  Changed: 3  Warnings: 0

mysql>
mysql> SELECT p.old_username, p.username, p.old_email, u.email
    -> FROM users u INNER JOIN people p ON p.id = u.id INNER JOIN entities e ON e.id = p.id
    -> WHERE u.id =1753671666 AND u.id !=506836355 AND e.sites_id =2846702735;
+--------------+------------+-----------+---------------------+
| old_username | username   | old_email | email               |
+--------------+------------+-----------+---------------------+
| NULL         | myusername | NULL      | [e-mail] |
+--------------+------------+-----------+---------------------+
1 row in set (0.00 sec)

mysql>
mysql> UPDATE
    -> users u INNER JOIN people p ON p.id = u.id INNER JOIN entities e ON e.id = p.id
    -> SET p.old_username = p.username, p.username = NULL
    -> WHERE u.id =1753671666 AND u.id !=506836355 AND e.sites_id =2846702735;
WHERE u.id =1753671666 AND u.id !=506836355 AND e.sites_id =2846702735Query OK, 1 row affected (0.00 sec);

Rows matched: 1  Changed: 1  Warnings: 0

mysql>
mysql> SELECT p.old_username, p.username, p.old_email, u.email
    -> FROM users u INNER JOIN people p ON p.id = u.id INNER JOIN entities e ON e.id = p.id
    -> WHERE u.id =1753671666 AND u.id !=506836355 AND e.sites_id =2846702735;
+--------------+----------+-----------+---------------------+
| old_username | username | old_email | email               |
+--------------+----------+-----------+---------------------+
| myusername   | NULL     | NULL      | [e-mail] |
+--------------+----------+-----------+---------------------+
1 row in set (0.00 sec)

mysql>
mysql> UPDATE
    -> users u INNER JOIN people p ON p.id = u.id INNER JOIN entities e ON e.id = p.id
    -> SET p.old_email = u.email, u.email = NULL
    -> WHERE u.id =1753671666 AND u.id !=506836355 AND e.sites_id =2846702735;
Query OK, 1 row affected (0.00 sec)
Rows matched: 2  Changed: 1  Warnings: 0

mysql>
mysql> SELECT p.old_username, p.username, p.old_email, u.email
    -> FROM users u INNER JOIN people p ON p.id = u.id INNER JOIN entities e ON e.id = p.id
    -> WHERE u.id =1753671666 AND u.id !=506836355 AND e.sites_id =2846702735;
+--------------+----------+-----------+-------+
| old_username | username | old_email | email |
+--------------+----------+-----------+-------+
| myusername   | NULL     | NULL      | NULL  |
+--------------+----------+-----------+-------+
1 row in set (0.00 sec)

mysql>
mysql> UPDATE
    -> users u INNER JOIN people p ON p.id = u.id INNER JOIN entities e ON e.id = p.id
    -> SET p.old_username = NULL, p.username = 'myusername',
    -> p.old_email = NULL, u.email = '[e-mail]',
    -> e.record_status = 'inactive', e.date_modified = NOW( ), e.modified_by_id =506836355
    -> WHERE u.id =1753671666 AND u.id !=506836355 AND e.sites_id =2846702735;
Query OK, 2 rows affected (0.00 sec)
Rows matched: 3  Changed: 2  Warnings: 0

mysql>
mysql> SELECT p.old_username, p.username, p.old_email, u.email
    -> FROM users u INNER JOIN people p ON p.id = u.id INNER JOIN entities e ON e.id = p.id
    -> WHERE u.id =1753671666 AND u.id !=506836355 AND e.sites_id =2846702735;
+--------------+------------+-----------+---------------------+
| old_username | username   | old_email | email               |
+--------------+------------+-----------+---------------------+
| NULL         | myusername | NULL      | [e-mail] |
+--------------+------------+-----------+---------------------+
1 row in set (0.00 sec)

mysql>
mysql> UPDATE
    -> users u INNER JOIN people p ON p.id = u.id INNER JOIN entities e ON e.id = p.id
    -> SET p.old_username = p.username, p.username = NULL,
    -> p.old_email = u.email, u.email = NULL,
    -> e.record_status = 'inactive', e.date_modified = NOW( ), e.modified_by_id =506836355
    -> WHERE u.id =1753671666 AND u.id !=506836355 AND e.sites_id =2846702735;
Query OK, 2 rows affected (0.00 sec)
Rows matched: 3  Changed: 2  Warnings: 0

mysql>
mysql> SELECT p.old_username, p.username, p.old_email, u.email
    -> FROM users u INNER JOIN people p ON p.id = u.id INNER JOIN entities e ON e.id = p.id
    -> WHERE u.id =1753671666 AND u.id !=506836355 AND e.sites_id =2846702735;
+--------------+----------+-----------+-------+
| old_username | username | old_email | email |
+--------------+----------+-----------+-------+
| myusername   | NULL     | NULL      | NULL  |
+--------------+----------+-----------+-------+
1 row in set (0.00 sec)

mysql>
mysql>

Answer 1

From the MySQL documentation :

The second assignment in the following expression sets col2 to the current (updated) value of col1, and not to the original value of col1. As a result, col1 and col2 have the same meaning. This behavior is different from standard SQL.

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

Single table UPDATE assignments are usually evaluated left to right. For updates with multiple tables, there is no guarantee that the assignments are made in any particular order.

, , .