<p>I have a table <code>story_category</code> in my database with corrupt entries. The next query returns the corrupt entries:</p>

<pre><code>SELECT * 
FROM  story_category 
WHERE category_id NOT IN (
    SELECT DISTINCT category.id 
    FROM category INNER JOIN 
       story_category ON category_id=category.id);
</code></pre>

<p>I tried to delete them executing:</p>

<pre><code>DELETE FROM story_category 
WHERE category_id NOT IN (
    SELECT DISTINCT category.id 
    FROM category 
      INNER JOIN story_category ON category_id=category.id);
</code></pre>

<p>But I get the next error: </p>

<blockquote>
  <p>#1093 - You can't specify target table 'story_category' for update in FROM clause </p>
</blockquote>

<p>How can I overcome this?</p>

[{'answer_id': 45498, 'author': 'Cheekysoft', 'author_id': 1820, 'author_profile': 'https://Stackoverflow.com/users/1820', 'pm_score': 11, 'selected': True, 'text': '<p><em>Update: This answer covers the general error classification. For a more specific answer about how to best handle the OP\'s exact query, please see other answers to this question</em></p>\n\n<p>In MySQL, you can\'t modify the same table which you use in the SELECT part.<br>\nThis behaviour is documented at:\n<a href="http://dev.mysql.com/doc/refman/5.6/en/update.html" rel="noreferrer">http://dev.mysql.com/doc/refman/5.6/en/update.html</a></p>\n\n<p><strong>Maybe you can just join the table to itself</strong></p>\n\n<p>If the logic is simple enough to re-shape the query, lose the subquery and join the table to itself, employing appropriate selection criteria. This will cause MySQL to see the table as two different things, allowing destructive changes to go ahead.</p>\n\n<pre><code>UPDATE tbl AS a\nINNER JOIN tbl AS b ON ....\nSET a.col = b.col\n</code></pre>\n\n<p><strong>Alternatively, try nesting the subquery deeper into a from clause ...</strong></p>\n\n<p>If you absolutely need the subquery, there\'s a workaround, but it\'s\nugly for several reasons, including performance:</p>\n\n<pre><code>UPDATE tbl SET col = (\n  SELECT ... FROM (SELECT.... FROM) AS x);\n</code></pre>\n\n<p>The nested subquery in the FROM clause creates an <em>implicit temporary\ntable</em>, so it doesn\'t count as the same table you\'re updating. </p>\n\n<p><strong>... but watch out for the query optimiser</strong></p>\n\n<p>However, beware that from <a href="http://dev.mysql.com/doc/relnotes/mysql/5.7/en/news-5-7-6.html" rel="noreferrer">MySQL 5.7.6</a> and onward, the optimiser may optimise out the subquery, and still give you the error. Luckily, the  <code>optimizer_switch</code> variable can be used to switch off this behaviour; although I couldn\'t recommend doing this as anything more than a short term fix, or for small one-off tasks.</p>\n\n<pre><code>SET optimizer_switch = \'derived_merge=off\';\n</code></pre>\n\n<p><em>Thanks to <a href="https://stackoverflow.com/users/345716/peter-v-m%C3%B8rch">Peter V. Mørch</a> for this advice in the comments.</em></p>\n\n<p>Example technique was from Baron Schwartz, <a href="http://grokbase.com/t/mysql/mysql/08259dm24b/error-you-cant-specify-target-table-for-update-in-from-clause" rel="noreferrer">originally published at Nabble</a>, paraphrased and extended here.</p>\n'}, {'answer_id': 45505, 'author': 'YonahW', 'author_id': 3821, 'author_profile': 'https://Stackoverflow.com/users/3821', 'pm_score': 3, 'selected': False, 'text': "<p>You could insert the desired rows' ids into a temp table and then delete all the rows that are found in that table.</p>\n\n<p>which may be what @Cheekysoft meant by doing it in two steps.</p>\n"}, {'answer_id': 694011, 'author': 'Tom Schaefer', 'author_id': 84136, 'author_profile': 'https://Stackoverflow.com/users/84136', 'pm_score': 1, 'selected': False, 'text': "<p>If something does not work, when coming thru the front-door, then take the back-door:</p>\n\n<pre><code>drop table if exists apples;\ncreate table if not exists apples(variety char(10) primary key, price int);\n\ninsert into apples values('fuji', 5), ('gala', 6);\n\ndrop table if exists apples_new;\ncreate table if not exists apples_new like apples;\ninsert into apples_new select * from apples;\n\nupdate apples_new\n    set price = (select price from apples where variety = 'gala')\n    where variety = 'fuji';\nrename table apples to apples_orig;\nrename table apples_new to apples;\ndrop table apples_orig;\n</code></pre>\n\n<p>It's fast. The bigger the data, the better.</p>\n"}, {'answer_id': 947417, 'author': 'Community', 'author_id': -1, 'author_profile': 'https://Stackoverflow.com/users/-1', 'pm_score': 7, 'selected': False, 'text': '<p>The <code>inner join</code> in your sub-query is unnecessary.  It looks like you want to delete the entries in <code>story_category</code> where the <code>category_id</code> is not in the <code>category</code> table.</p>\n<p>Instead of that:</p>\n<pre><code>DELETE FROM story_category \nWHERE category_id NOT IN (\n    SELECT DISTINCT category.id \n    FROM category INNER JOIN\n         story_category ON category_id=category.id);\n</code></pre>\n<p>Do this:</p>\n<pre><code>DELETE FROM story_category \nWHERE category_id NOT IN (\n    SELECT DISTINCT category.id \n    FROM category);\n</code></pre>\n'}, {'answer_id': 3624656, 'author': 'sactiw', 'author_id': 416369, 'author_profile': 'https://Stackoverflow.com/users/416369', 'pm_score': 4, 'selected': False, 'text': "<p>This is what I did for updating a Priority column value by 1 if it is >=1 in a table and in its WHERE clause using a subquery on same table to make sure that at least one row contains Priority=1 (because that was the condition to be checked while performing update) :</p>\n\n<pre><code>\nUPDATE My_Table\nSET Priority=Priority + 1\nWHERE Priority >= 1\nAND (SELECT TRUE FROM (SELECT * FROM My_Table WHERE Priority=1 LIMIT 1) as t);\n</code></pre>\n\n<p>I know it's a bit ugly but it does works fine.</p>\n"}, {'answer_id': 8620162, 'author': 'NexusRex', 'author_id': 445500, 'author_profile': 'https://Stackoverflow.com/users/445500', 'pm_score': 5, 'selected': False, 'text': '<pre><code>DELETE FROM story_category\nWHERE category_id NOT IN (\n    SELECT cid FROM (\n        SELECT DISTINCT category.id AS cid FROM category INNER JOIN story_category ON category_id=category.id\n    ) AS c\n)\n</code></pre>\n'}, {'answer_id': 9843719, 'author': 'Yehor', 'author_id': 1101589, 'author_profile': 'https://Stackoverflow.com/users/1101589', 'pm_score': 9, 'selected': False, 'text': '<p><a href="https://stackoverflow.com/users/445500/nexusrex">NexusRex</a> provided  a <a href="https://stackoverflow.com/a/8620162/1101589">very good solution</a> for deleting with join from the same table.</p>\n\n<p>If you do this:</p>\n\n<pre><code>DELETE FROM story_category\nWHERE category_id NOT IN (\n        SELECT DISTINCT category.id AS cid FROM category \n        INNER JOIN story_category ON category_id=category.id\n)\n</code></pre>\n\n<p>you are going to get an error.</p>\n\n<p>But if you wrap the condition in one more select:</p>\n\n<pre><code>DELETE FROM story_category\nWHERE category_id NOT IN (\n    SELECT cid FROM (\n        SELECT DISTINCT category.id AS cid FROM category \n        INNER JOIN story_category ON category_id=category.id\n    ) AS c\n)\n</code></pre>\n\n<p>it would do the right thing!!</p>\n\n<p><strong>Explanation:</strong> The query optimizer does a <a href="https://mariadb.com/kb/en/library/derived-table-merge-optimization/" rel="noreferrer"><em>derived merge optimization</em></a> for the first query (which causes it to fail with the error), but the second query doesn\'t qualify for the <em>derived merge optimization</em>. Hence the optimizer is forced to execute the subquery first.</p>\n'}, {'answer_id': 12737534, 'author': 'Pratik Khadloya', 'author_id': 238880, 'author_profile': 'https://Stackoverflow.com/users/238880', 'pm_score': 7, 'selected': False, 'text': "<p>Recently i had to update records in the same table i did it like below:</p>\n\n<pre><code>UPDATE skills AS s, (SELECT id  FROM skills WHERE type = 'Programming') AS p\nSET s.type = 'Development' \nWHERE s.id = p.id;\n</code></pre>\n"}, {'answer_id': 27841102, 'author': 'briankip', 'author_id': 536434, 'author_profile': 'https://Stackoverflow.com/users/536434', 'pm_score': 2, 'selected': False, 'text': '<p>According to <a href="http://dev.mysql.com/doc/refman/5.6/en/update.html" rel="nofollow"> the Mysql UPDATE Syntax</a> linked by @CheekySoft, it says right at the bottom.</p>\n\n<blockquote>\n  <p>Currently, you cannot update a table and select from the same table in a subquery.</p>\n</blockquote>\n\n<p>I guess you are deleting from store_category while still selecting from it in the union.</p>\n'}, {'answer_id': 37544632, 'author': 'lipika chakraborty', 'author_id': 6400165, 'author_profile': 'https://Stackoverflow.com/users/6400165', 'pm_score': 2, 'selected': False, 'text': '<p>Try to save result of Select statement in separate variable and then use that for delete query.</p>\n'}, {'answer_id': 43610081, 'author': 'Sequoya', 'author_id': 4389068, 'author_profile': 'https://Stackoverflow.com/users/4389068', 'pm_score': 7, 'selected': False, 'text': "<p>If you can't do </p>\n\n<pre><code>UPDATE table SET a=value WHERE x IN\n    (SELECT x FROM table WHERE condition);\n</code></pre>\n\n<p>because it is the same table, you can trick and do :</p>\n\n<pre><code>UPDATE table SET a=value WHERE x IN\n    (SELECT * FROM (SELECT x FROM table WHERE condition) as t)\n</code></pre>\n\n<p>[update or delete or whatever]</p>\n"}, {'answer_id': 43905253, 'author': 'Melvin Angelo Jabonillo', 'author_id': 7990568, 'author_profile': 'https://Stackoverflow.com/users/7990568', 'pm_score': 1, 'selected': False, 'text': '<p>how about this query hope it helps</p>\n\n<pre><code>DELETE FROM story_category LEFT JOIN (SELECT category.id FROM category) cat ON story_category.id = cat.id WHERE cat.id IS NULL\n</code></pre>\n'}, {'answer_id': 48198876, 'author': 'S.Roshanth', 'author_id': 2783908, 'author_profile': 'https://Stackoverflow.com/users/2783908', 'pm_score': 3, 'selected': False, 'text': '<p>The simplest way to do this is use a table alias when you are referring parent query table inside the sub query.</p>\n\n<p>Example :</p>\n\n<pre><code>insert into xxx_tab (trans_id) values ((select max(trans_id)+1 from xxx_tab));\n</code></pre>\n\n<p>Change it to:</p>\n\n<pre><code>insert into xxx_tab (trans_id) values ((select max(P.trans_id)+1 from xxx_tab P));\n</code></pre>\n'}, {'answer_id': 48392742, 'author': 'Sameer Khanal', 'author_id': 5836015, 'author_profile': 'https://Stackoverflow.com/users/5836015', 'pm_score': 2, 'selected': False, 'text': '<p>try this</p>\n\n<pre><code>DELETE FROM story_category \nWHERE category_id NOT IN (\nSELECT DISTINCT category.id \nFROM (SELECT * FROM STORY_CATEGORY) sc;\n</code></pre>\n'}, {'answer_id': 56978014, 'author': 'Doin', 'author_id': 999120, 'author_profile': 'https://Stackoverflow.com/users/999120', 'pm_score': 4, 'selected': False, 'text': '<p>For the specific query the OP is trying to achieve, the ideal and most efficient way to do this is NOT to use a subquery at all.  </p>\n\n<p>Here are the <code>LEFT JOIN</code> versions of the OP\'s two queries:</p>\n\n<pre class="lang-sql prettyprint-override"><code>SELECT s.* \nFROM story_category s \nLEFT JOIN category c \nON c.id=s.category_id \nWHERE c.id IS NULL;\n</code></pre>\n\n<p>Note: <code>DELETE s</code> restricts delete operations to the <code>story_category</code> table.<br/><a href="https://dev.mysql.com/doc/refman/5.7/en/delete.html#idm45469393574032" rel="noreferrer">Documentation</a></p>\n\n<pre class="lang-sql prettyprint-override"><code>DELETE s \nFROM story_category s \nLEFT JOIN category c \nON c.id=s.category_id \nWHERE c.id IS NULL;\n</code></pre>\n'}, {'answer_id': 61138675, 'author': 'GMB', 'author_id': 10676716, 'author_profile': 'https://Stackoverflow.com/users/10676716', 'pm_score': 1, 'selected': False, 'text': '<p>As far as concerns, you want to delete rows in <code>story_category</code> that do not exist in <code>category</code>.</p>\n\n<p>Here is your original query to identify the rows to delete:</p>\n\n<pre><code>SELECT * \nFROM  story_category \nWHERE category_id NOT IN (\n    SELECT DISTINCT category.id \n    FROM category INNER JOIN \n       story_category ON category_id=category.id\n);\n</code></pre>\n\n<p>Combining <code>NOT IN</code> with a subquery that <code>JOIN</code>s the original table seems unecessarily convoluted. This can  be expressed in a more straight-forward manner with <code>not exists</code> and a correlated subquery:</p>\n\n<pre><code>select sc.*\nfrom story_category sc\nwhere not exists (select 1 from category c where c.id = sc.category_id);\n</code></pre>\n\n<p>Now it is easy to turn this to a <code>delete</code> statement:</p>\n\n<pre><code>delete from story_category\nwhere not exists (select 1 from category c where c.id = story_category.category_id);    \n</code></pre>\n\n<p>This quer would run on any MySQL version, as well as in most other databases that I know.</p>\n\n<p><strong><a href="https://dbfiddle.uk/?rdbms=mysql_5.7&amp;fiddle=d73925fbe6a24605b1cf9fe66a94d07c" rel="nofollow noreferrer">Demo on DB Fiddle</a></strong>:</p>\n\n<pre><code>-- set-up\ncreate table story_category(category_id int);\ncreate table category (id int);\ninsert into story_category values (1), (2), (3), (4), (5);\ninsert into category values (4), (5), (6), (7);\n\n-- your original query to identify offending rows\nSELECT * \nFROM  story_category \nWHERE category_id NOT IN (\n    SELECT DISTINCT category.id \n    FROM category INNER JOIN \n       story_category ON category_id=category.id);\n</code></pre>\n\n<pre>\n| category_id |\n| ----------: |\n|           1 |\n|           2 |\n|           3 |\n</pre>\n\n<pre><code>-- a functionally-equivalent, simpler query for this\nselect sc.*\nfrom story_category sc\nwhere not exists (select 1 from category c where c.id = sc.category_id)\n</code></pre>\n\n<pre>\n| category_id |\n| ----------: |\n|           1 |\n|           2 |\n|           3 |\n</pre>\n\n<pre><code>-- the delete query\ndelete from story_category\nwhere not exists (select 1 from category c where c.id = story_category.category_id);\n\n-- outcome\nselect * from story_category;\n</code></pre>\n\n<pre>\n| category_id |\n| ----------: |\n|           4 |\n|           5 |\n</pre>\n'}]
['https://Stackoverflow.com/questions/45494', 'https://Stackoverflow.com', 'https://Stackoverflow.com/users/2138/']