Nice Rik! On Thu, Sep 8, 2011 at 3:19 PM, Rik Wasmus <r...@grib.nl> wrote:
> > 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=sureshkumar...@gmail.com > > -- Thanks Suresh Kuna MySQL DBA
