MySql subquery runs instead of returning error

I faced some weird mysql subquery issues ...

UPDATE site_plugin_products_items 
    SET item_distributor = 176
WHERE item_id in ( 
    SELECT item_id 
    FROM `site_plugin_products_field_values` 
    WHERE value_val in ( 554, 1384  , 785 )
)

I the problem is:

Result that query updated ENTIRE table site_plugin_products_items with item_distributor = 176

If i ran the subquery individual:

SELECT item_id 
FROM `site_plugin_products_field_values` 
WHERE value_val in ( 554, 1384  , 785 )

I get the following error because i put the wrong field, it was supposed to be value_product, not item_id

Error in query (1054): Unknown column 'item_id' in 'field list'

Why did this happen ? Shouldn't the big query return an error instead of messing the entire table ?

Lost 2 years when I saw the result, and didn't know if I have a close backup :)

Solution

In the query -

UPDATE site_plugin_products_items 
    SET item_distributor = 176
WHERE item_id in ( 
    SELECT item_id 
    FROM `site_plugin_products_field_values` 
    WHERE value_val in ( 554, 1384  , 785 )
)

As item_id doesn't exist in the site_plugin_products_field_values table, it will use the column from the site_plugin_products_items table.

As this will always be the same as the condition (item_id will always equal itself) it will update every row.

I tried something to prove this (to myself) using...

SELECT * FROM `order` WHERE id in ( select customerID from user u)

This runs, but the user table doesn't have a customerID. BUT if I add the alias to the customerID field...

SELECT * FROM `order` WHERE id in ( select u.customerID from user u)

This fails.

Simple solution - use aliases to ensure which table your using per column and use the right columns in the first place. Although I won't mention backups!