Description:
mysql> update x
-> set email = 'igor@yahoo.com'
-> where id = 1 and date_format(date_received, '%Y%m%d') = 20040429;
The query above is supposed to update email when the following conditions are met (id = 1) and date_received = 20040429. The problem is that the query updates not only the email column but also the date_received column to NOW().
How to repeat:
mysql> create table x (id integer, date_received timestamp(14), email varchar(255));
Query OK, 0 rows affected (0.00 sec)
mysql> insert into x (id, date_received, email) values (1, now()-1, 'bill@yahoo.com');
Query OK, 1 row affected (0.02 sec)
mysql> select * from x;
+------+----------------+----------------+
| id | date_received | email |
+------+----------------+----------------+
| 1 | 20040429180911 | bill@yahoo.com |
+------+----------------+----------------+
1 row in set (0.00 sec)
mysql> insert into x (id, date_received, email) values (2, now()-2, 'tom@yahoo.com');
Query OK, 1 row affected (0.00 sec)
mysql> insert into x (id, date_received, email) values (3, now()-3, 'john@yahoo.com');
Query OK, 1 row affected (0.00 sec)
mysql> select * from x;
+------+----------------+----------------+
| id | date_received | email |
+------+----------------+----------------+
| 1 | 20040429180911 | bill@yahoo.com |
| 2 | 20040429180932 | tom@yahoo.com |
| 3 | 20040429180945 | john@yahoo.com |
+------+----------------+----------------+
3 rows in set (0.00 sec)
mysql> update x
-> set email = 'igor@yahoo.com'
-> where id = 1 and date_format(date_received, '%Y%m%d') = 20040429;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> select * from x;
+------+----------------+----------------+
| id | date_received | email |
+------+----------------+----------------+
| 1 | 20040429181341 | igor@yahoo.com |
| 2 | 20040429180932 | tom@yahoo.com |
| 3 | 20040429180945 | john@yahoo.com |
+------+----------------+----------------+
3 rows in set (0.01 sec)
Notice that date_received has changed after query has been executed.
Description: mysql> update x -> set email = 'igor@yahoo.com' -> where id = 1 and date_format(date_received, '%Y%m%d') = 20040429; The query above is supposed to update email when the following conditions are met (id = 1) and date_received = 20040429. The problem is that the query updates not only the email column but also the date_received column to NOW(). How to repeat: mysql> create table x (id integer, date_received timestamp(14), email varchar(255)); Query OK, 0 rows affected (0.00 sec) mysql> insert into x (id, date_received, email) values (1, now()-1, 'bill@yahoo.com'); Query OK, 1 row affected (0.02 sec) mysql> select * from x; +------+----------------+----------------+ | id | date_received | email | +------+----------------+----------------+ | 1 | 20040429180911 | bill@yahoo.com | +------+----------------+----------------+ 1 row in set (0.00 sec) mysql> insert into x (id, date_received, email) values (2, now()-2, 'tom@yahoo.com'); Query OK, 1 row affected (0.00 sec) mysql> insert into x (id, date_received, email) values (3, now()-3, 'john@yahoo.com'); Query OK, 1 row affected (0.00 sec) mysql> select * from x; +------+----------------+----------------+ | id | date_received | email | +------+----------------+----------------+ | 1 | 20040429180911 | bill@yahoo.com | | 2 | 20040429180932 | tom@yahoo.com | | 3 | 20040429180945 | john@yahoo.com | +------+----------------+----------------+ 3 rows in set (0.00 sec) mysql> update x -> set email = 'igor@yahoo.com' -> where id = 1 and date_format(date_received, '%Y%m%d') = 20040429; Query OK, 1 row affected (0.00 sec) Rows matched: 1 Changed: 1 Warnings: 0 mysql> select * from x; +------+----------------+----------------+ | id | date_received | email | +------+----------------+----------------+ | 1 | 20040429181341 | igor@yahoo.com | | 2 | 20040429180932 | tom@yahoo.com | | 3 | 20040429180945 | john@yahoo.com | +------+----------------+----------------+ 3 rows in set (0.01 sec) Notice that date_received has changed after query has been executed.