> 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 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=arch...@jab.org
