Mysql – Error trying to SET a field when two fields in different tables match

MySQL

I am trying to set catid to 584 whenever the rows in the tables ykz2d_crmery_deals and ykz2d_maps_integration have the same company id using the following query:

UPDATE ykz2d_maps_integration 
SET catid = '584' 
WHERE ykz2d_crmery_deals.company_id = ykz2d_maps_integration.company_id

I keep getting the following error:

Failed to execute SQL : SQL UPDATE ykz2d_maps_integration SET catid = '584' WHERE ykz2d_crmery_deals.company_id = ykz2d_maps_integration.company_id failed : Unknown column 'ykz2d_crmery_deals.company_id' in 'where clause'

The column company_id does exist within ykz2d_crmery_deals.

What I can do to fix this?

Best Answer

UPDATE ykz2d_maps_integration , ykz2d_crmery_deals
SET catid = '584' 
where ykz2d_crmery_deals.company_id = ykz2d_maps_integration.company_id
;

The table must be listed in the update clause to avoi the error message

Related Solutions

MySQL error in where clause : Unknown column

Now, if you rewrite the query in a clear manner, you can see where's the troublesome column. From what I know of general SQL you can't use the alias of the column in the WHERE clause (but you can use it in the ORDER BY clause).

So you'd have to rewrite the query something like:

Select *
from (
    -- Your nice query without the have_discount filter
) as Aux
WHERE Aux.have_discount = 1
ORDER BY date_activated DESC
LIMIT 0,12

MySQL foreign key error

You're getting this error because you're trying to update a row to service table that does not have a valid value for the company_id field based on the values currently stored in company table.

If you post some more code someone can help you diagnose the specific cause.

Best Answer

Related Solutions

MySQL error in where clause : Unknown column

MySQL foreign key error

Related Question