MySQL You can't specify target table X for update in FROM clause

0
votes

Any SQL gurus out there who can rewrite the following query:

UPDATE cmsTemplate 
   SET master = NULL 
 WHERE master IS NOT NULL 
   AND master NOT IN (SELECT nodeId 
                        FROM 
                           ( SELECT * FROM cmsTemplate a) b
                     )

So that it does not produce the following error:

You can't specify target table 'cmsTemplate' for update in FROM clause

Issue documented here:

http://dev.mysql.com/doc/refman/5.6/en/update.html

Thanks, Steve

UPDATE: Description of query

The idea of the query is to do as follows:

  1. SET the master field TO NULL
  2. WHERE the master field IS NOT NULL
  3. AND WHERE the master field IS NOT EQUAL TO ANY of the values for the nodeId field records (within the same table)
1
mysqlsql-updatesyntax-error

1 Answers

2
votes

You can use update with join:

update cmsTemplate c1 left join cmsTemplate c2
on c1.`master` = c2.nodeId
set c1.`master` = null
where c2.nodeId is null;