[RESOLVED] sub-select error about specifying a table in the FROM

[jrough]

UPDATE cart_inventory_temp as ci
SET error_flag=1

WHERE ci.nationalDrugCode NOT IN(SELECT ndc FROM onelink as ol
INNER JOIN cart_inventory_temp as ci
WHERE (ol.ndc=ci.nationalDrugCode OR ol.sku=ci.sku))
The error code is:

Error Code: 1093
You can't specify target table 'ci' for update in FROM clause

So I want to mark all the ones with errors. The ones that there is no matching ci.nationalDrugCode with o.ndc, the ndc is valid if there is a match on either the ndc or the sku. The sub-select works, the update query has the error. tia,

[dagon]

at a wild guess try not using he same alias "ci" in the sub query and main query

[jrough]

hi dagon,
There is a limitation on this type of subselect. You can do this in Oracle.
This is one weird limitation, see bottom of page:
http://dev.mysql.com/doc/refman/5.0/en/subquery-errors.html