SQL Query not working after using it for 3 years without issues, [solved]

[mailblade]

I've been running the below piece of code in an event scheduler for the past 3 years without any issue.

Code: [Select]

UPDATE `jos_virtuemart_product_customfields` SET `customfield_value`= `customfield_value` - 1 WHERE `virtuemart_custom_id` = 22 AND `customfield_value` !="0"
However, since yesterday, I have been getting the following error:

#1292 - Truncated incorrect DOUBLE value: ''

I haven't changed anything in my Virtuemart DB or done anything remotely different. Has anyone experienced this before ?

[AH]

Tested here and seems to work fine

[Jörgen]

Some googling

This message means you're trying to compare a number and a string in a WHERE clause; either make sure they have similar declarations, or use an explicit CAST to convert the number to a string. If you turn off strict mode, the error should turn into a warning.Jun 30, 2016

Jörgen @ Kreativ Fotografi

[mailblade]

@AH thank you, glad it is still working. Must be something wrong on my end then.

@Jorgen: I also found the above page, thanks for providing the quotes. I will try fixing it.

[Jörgen]

Tell us how it goes.

Jörgen @ Kreativ Fotografi

[mailblade]

@Jurgen: I just ran it again and all seems back to normal. That was pretty weird 

I did re-upload all the customfield_values where virtuemart_custom_id = 22 yesterday afternoon, so maybe that helped to sort it out.

[Jörgen]

Nice to hear that it works again, pity that the cause remains some what unknown.

Jörgen @ Kreativ Fotografi

[mailblade]

I found the reason why it was not working!

There was one null value in the customfield_values where virtuemart_custom_id = 22, so it was impossible to minus this value by "1".  This is why I got the error!

[AH]

Great - thanks for the update