Re: using the same field to be set in the update query and in the where clause

Peter H. Coffin wrote:

> […] the unstated thing that kushal wanted to do was trip a a
> timestamp defined ON UPDATE CURRENT_TIMESTAMP. And if the row doesn't in
> fact update ("0 rows updated") then what I remember (not) happening is
> that the ON UPDATE doesn't trip and the timestamp is not updated.

You remember correctly:

,----
|
| mysql> ALTER TABLE `tmp` ADD COLUMN `ts` TIMESTAMP ON UPDATE CURRENT_TIMESTAMP;
| Query OK, 0 rows affected (0.13 sec)
| Records: 0 Duplicates: 0 Warnings: 0
|
| mysql> SELECT `id`, `year`, `ts` FROM `tmp` WHERE `id` = 20;
| +----+------+---------------------+
| | id | year | ts |
| +----+------+---------------------+
| | 20 | 1905 | 0000-00-00 00:00:00 |
| +----+------+---------------------+
| 1 row in set (0.00 sec)
|
| mysql> UPDATE `tmp` SET `year`=1905 WHERE `id` = 20;
| Query OK, 0 rows affected (0.00 sec)
| Rows matched: 1 Changed: 0 Warnings: 0
|
| mysql> SELECT `id`, `year`, `ts` FROM `tmp` WHERE `id` = 20;
| +----+------+---------------------+
| | id | year | ts |
| +----+------+---------------------+
| | 20 | 1905 | 0000-00-00 00:00:00 |
| +----+------+---------------------+
| 1 row in set (0.00 sec)
|
| mysql> UPDATE `tmp` SET `year`=2005 WHERE `id` = 20;
| Query OK, 1 row affected (0.00 sec)
| Rows matched: 1 Changed: 1 Warnings: 0
|
| mysql> SELECT `id`, `year`, `ts` FROM `tmp` WHERE `id` = 20;
| +----+------+---------------------+
| | id | year | ts |
| +----+------+---------------------+
| | 20 | 2005 | 2017-03-13 18:07:00 |
| +----+------+---------------------+
| 1 row in set (0.00 sec)
|
| mysql> SELECT VERSION();
| +-----------+
| | VERSION() |
| +-----------+
| | 5.6.25-3 |
| +-----------+
| 1 row in set (0.00 sec)
|

`----

-- 
PointedEars

Twitter: _at_PointedEars2
Please do not cc me. / Bitte keine Kopien per E-Mail.