MySQL Truncated incorrect DOUBLE value error

Question

I'm trying to create a MySQL query to update all of the product variations tax class in Woocommerce.

Using the query below I keep getting this error and its not something I've come across before, google seems to offer a variety of answers but I'm unable to relate the fix to my query.

I'm getting the error - #1292 - Truncated incorrect DOUBLE value: 'product'?

I don't understand where the value of product comes from in the error?

UPDATE wp_postmeta a 
   JOIN wp_posts b ON b.id = a.post_id  
   SET a.meta_value = 'test456'

WHERE b.post_type = 'product_variation' & a.meta_key = '_tax_class'

Thanks for reading

Answer 1

Try this:

UPDATE wp_postmeta a 
JOIN wp_posts b ON b.id = a.post_id  
SET a.meta_value = 'test456'
WHERE b.post_type = 'product_variation' and a.meta_key = '_tax_class'

Answer 2

First, & is not AND. It is bitwise AND. You want the boolean variety.

UPDATE wp_postmeta pm JOIN
       wp_posts p
       ON p.id = pm.post_id  
   SET pm.meta_value = 'test456'
   WHERE p.post_type = 'product_variation' AND
         pm.meta_key = '_tax_class'

Because of the rules of MySQL, I think is interpreted as:

   WHERE ((p.post_type = ('product_variation' & pm.meta_key) ) = '_tax_class'

(or something similar). The key point is that the values are not interpreted as you expect. And, numbers are getting involved because boolean expressions are treated as numbers in a numeric context.