MySQL 1093 error message

1143015 pts.
Tags:
Database
MySQL
In MySQL database, I have a table that have corrupt entries. Here's the query that returns the entries:
SELECT * 
FROM  story_category 
WHERE category_id NOT IN (
    SELECT DISTINCT category.id 
    FROM category INNER JOIN 
       story_category ON category_id=category.id);
So, here's how I tried to delete them:
DELETE FROM story_category 
WHERE category_id NOT IN (
    SELECT DISTINCT category.id 
    FROM category 
      INNER JOIN story_category ON category_id=category.id);
But I'm getting this error: #1093 - You can't specify target table 'story_category' for update in FROM clause. What can I do here?
1

Answer Wiki

Thanks. We'll let you know when a new response is added.

you can do multi-table updates like this:

UPDATE tbl AS a
  INNER JOIN tbl AS b ON ….
  SET a.col = b.col

If you absolutely need the subquery, there’s a workaround, but it’s ugly for several reasons, including performance:

UPDATE tbl SET col = (
  SELECT … FROM (SELECT…. FROM) AS x);

The nested subquery in the FROM clause creates an implicit temporary table, so it doesn’t count as the same table you’re updating.
Read more

Discuss This Question:  

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

Thanks! We'll email you when relevant content is added and updated.

Following

Share this item with your network: