> I fired the update statement in a wrong way ..like this ..
>
> update user_info set login_date='2011-08-05 04:15:05' and user_id =16078845
> limit 1 ;
> ( I forgot to use where . instead of where I used and )
> update user_info set login_date='2011-08-05 04:15:05' where user_id
> =16078845 limit 1 ; ( this is the query intended )
>
> after the update ..I got this message ..
> mysql> update user_info set login_date='2011-08-05 04:15:05' and user_id
> =16078845 limit 1;
> Query OK, 1 row affected, 1 warning (0.02 sec)
> Rows matched: 1 Changed: 1 Warnings: 0
>
> It shows that one record is affected and one row changed ..
> I did show warnings ..the output is like this ..
>
> | Warning | 1292 | Truncated incorrect DOUBLE value: '2011-08-05 04:15:05'
> So my question is what happened exactly ?
> Why no records updated ?
A lot of casting:
(1) login_date='2011-08-05 04:15:05' and user_id =16078845;
And implies boolean, so the result is the either true or false. MySQL doesn't
like using non-numbers as booleans (the '2011-08-05 04:15:05') , this is the
double spoken of.
(2) login_date = false (or true, but that doesn't matter)
But MySQL doesn't know booleans, to a number it is:
(3) login_date = 0
But the column is a DATETIME (or TIMESTAMP) column, 0 or 1 is an incorrect
value, cast to:
(4) login_date = 0000-00-00 00:00:00
So, somewhere there's (or was, may be overwritten) a record with that value,
just 1 due to the limit 1, otherwise, the whole table would have that as a
login_date (doesn't matter wether it was true or false).
Check out:
DB 5.1.58-1-log:(none) mysql> SELECT 1 AND 1;
+---------+
| 1 AND 1 |
+---------+
| 1 |
+---------+
1 row in set (0.00 sec)
DB 5.1.58-1-log:(none) mysql> SELECT 0 AND 1;
+---------+
| 0 AND 1 |
+---------+
| 0 |
+---------+
1 row in set (0.01 sec)
DB 5.1.58-1-log:(none) mysql> SELECT '1' AND 1;
+-----------+
| '1' AND 1 |
+-----------+
| 1 |
+-----------+
1 row in set (0.03 sec)
DB 5.1.58-1-log:(none) mysql> SELECT 'a' AND 1;
+-----------+
| 'a' AND 1 |
+-----------+
| 0 |
+-----------+
1 row in set, 1 warning (0.03 sec)
DB 5.1.58-1-log:(none) mysql> SHOW WARNINGS;
+---------+------+---------------------------------------+
| Level | Code | Message |
+---------+------+---------------------------------------+
| Warning | 1292 | Truncated incorrect DOUBLE value: 'a' |
+---------+------+---------------------------------------+
1 row in set (0.01 sec)
--
Rik Wasmus
