Search code examples
mysqlmysql-error-1064

Update query using one of its fields in a select query in where clause


I know it's a confusing title, but the example will show what I want to achieve:

UPDATE tr.tbl_to_be_updated SET cat=-1 WHERE cat NOT IN (SELECT c.id
  FROM tr.cat as c WHERE c.sh=tbl_to_be_updated.sh))

How can I achieve this? With this query I receive an error that it's not allowed.


Solution

  • UPDATE tbl_to_be_updated 
    LEFT JOIN cat ON cat.sh = tbl_to_be_updated.sh
    SET tbl_to_be_updated.cat = -1 
    WHERE cat.sh IS NULL