MYSQL Truncated incorrect DOUBLE value

Question

You don't need the AND keyword. Here's the correct syntax of the UPDATE statement:

UPDATE 
    shop_category 
SET 
    name = 'Secolul XVI - XVIII', 
    name_eng = '16th to 18th centuries' 
WHERE 
    category_id = 4768

I was getting this exception not because of AND instead of comma, in fact I was having this exception just because I was not using apostrophes in where clause.

Like my query was

update table set coulmn1='something' where column2 in (00012121);

when I changed where clause to where column2 in ('00012121'); then the query worked fine for me.

What it basically is

It's incorrect syntax that causes MySQL to think you're trying to do something with a column or parameter that has the incorrect type "DOUBLE".

Learn from my mistake

In my case I updated the varchar column in a table setting NULL where the value 0 stood. My update query was like this:

UPDATE myTable SET myValue = NULL WHERE myValue = 0;

Now, since the actual type of myValue is VARCHAR(255) this gives the warning:

+---------+------+-----------------------------------------------+
| Level   | Code | Message                                       |
+---------+------+-----------------------------------------------+
| Warning | 1292 | Truncated incorrect DOUBLE value: 'value xyz' |
+---------+------+-----------------------------------------------+

And now myTable is practically empty, because myValue is now NULL for EVERY ROW in the table! How did this happen?
^{*internal screaming*}

Over 30k rows now have missing data.
^{*internal screaming intensifies*}

Thank goodness for backups. I was able to recover all the data.
^{*internal screaming intensity lowers*}

The corrected query is as follows:

UPDATE myTable SET myValue = NULL WHERE myValue = '0';
                                                  ^^^
                                                  Quotation here!

I wish this was more than just a warning so it's less dangerous to forget those quotes.

^{*End internal screaming*}

Try replacing the AND with ,

UPDATE shop_category 
SET name = 'Secolul XVI - XVIII', name_eng = '16th to 18th centuries' 
WHERE category_id = 4768

The UPDATE Syntax shows comma should be used as the separator.

Mainly invalid query strings will give this warning.

Wrong due to a subtle syntax error (misplaced right parenthesis) when using INSTR function:

INSERT INTO users (user_name) SELECT name FROM site_users WHERE
INSTR(status, 'active'>0);

Correct:

INSERT INTO users (user_name) SELECT name FROM site_users WHERE
INSTR(status, 'active')>0;