MySQL Error 1093 - Can't specify target table for update in FROM clause

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

In MySQL, you can't modify the same table which you use in the SELECT part.
This behaviour is documented at: http://dev.mysql.com/doc/refman/5.6/en/update.html

Maybe you can just join the table to itself

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.

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

Alternatively, try nesting the subquery deeper into a from clause ...

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.

... but watch out for the query optimiser

However, beware that from MySQL 5.7.6 and onward, the optimiser may optimise out the subquery, and still give you the error. Luckily, the optimizer_switch 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.

SET optimizer_switch = 'derived_merge=off';

Thanks to Peter V. Mørch for this advice in the comments.

Example technique was from Baron Schwartz, originally published at Nabble, paraphrased and extended here.

NexusRex provided a very good solution for deleting with join from the same table.

If you do this:

DELETE FROM story_category
WHERE category_id NOT IN (
        SELECT DISTINCT category.id AS cid FROM category 
        INNER JOIN story_category ON category_id=category.id
)

you are going to get an error.

But if you wrap the condition in one more select:

DELETE FROM story_category
WHERE category_id NOT IN (
    SELECT cid FROM (
        SELECT DISTINCT category.id AS cid FROM category 
        INNER JOIN story_category ON category_id=category.id
    ) AS c
)

it would do the right thing!!

Explanation: The query optimizer does a derived merge optimization for the first query (which causes it to fail with the error), but the second query doesn't qualify for the derived merge optimization. Hence the optimizer is forced to execute the subquery first.

The inner join in your sub-query is unnecessary. It looks like you want to delete the entries in story_category where the category_id is not in the category table.

Instead of that:

DELETE FROM story_category 
WHERE category_id NOT IN (
    SELECT DISTINCT category.id 
    FROM category INNER JOIN
         story_category ON category_id=category.id);

Do this:

DELETE FROM story_category 
WHERE category_id NOT IN (
    SELECT DISTINCT category.id 
    FROM category);

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.

Here are the LEFT JOIN versions of the OP's two queries:

SELECT s.* 
FROM story_category s 
LEFT JOIN category c 
ON c.id=s.category_id 
WHERE c.id IS NULL;

Note: DELETE s restricts delete operations to the story_category table.
Documentation

DELETE s 
FROM story_category s 
LEFT JOIN category c 
ON c.id=s.category_id 
WHERE c.id IS NULL;

The simplest way to do this is use a table alias when you are referring parent query table inside the sub query.

Example :

insert into xxx_tab (trans_id) values ((select max(trans_id)+1 from xxx_tab));

Change it to:

insert into xxx_tab (trans_id) values ((select max(P.trans_id)+1 from xxx_tab P));

You could insert the desired rows' ids into a temp table and then delete all the rows that are found in that table.

which may be what @Cheekysoft meant by doing it in two steps.

try this

DELETE FROM story_category 
WHERE category_id NOT IN (
SELECT DISTINCT category.id 
FROM (SELECT * FROM STORY_CATEGORY) sc;

If something does not work, when coming thru the front-door, then take the back-door:

drop table if exists apples;
create table if not exists apples(variety char(10) primary key, price int);

insert into apples values('fuji', 5), ('gala', 6);

drop table if exists apples_new;
create table if not exists apples_new like apples;
insert into apples_new select * from apples;

update apples_new
    set price = (select price from apples where variety = 'gala')
    where variety = 'fuji';
rename table apples to apples_orig;
rename table apples_new to apples;
drop table apples_orig;

It's fast. The bigger the data, the better.

As far as concerns, you want to delete rows in story_category that do not exist in category.

Here is your original query to identify the rows to delete:

SELECT * 
FROM  story_category 
WHERE category_id NOT IN (
    SELECT DISTINCT category.id 
    FROM category INNER JOIN 
       story_category ON category_id=category.id
);

Combining NOT IN with a subquery that JOINs the original table seems unecessarily convoluted. This can be expressed in a more straight-forward manner with not exists and a correlated subquery:

select sc.*
from story_category sc
where not exists (select 1 from category c where c.id = sc.category_id);

Now it is easy to turn this to a delete statement:

delete from story_category
where not exists (select 1 from category c where c.id = story_category.category_id);    

This quer would run on any MySQL version, as well as in most other databases that I know.

Demo on DB Fiddle:

-- set-up
create table story_category(category_id int);
create table category (id int);
insert into story_category values (1), (2), (3), (4), (5);
insert into category values (4), (5), (6), (7);

-- your original query to identify offending rows
SELECT * 
FROM  story_category 
WHERE category_id NOT IN (
    SELECT DISTINCT category.id 
    FROM category INNER JOIN 
       story_category ON category_id=category.id);

| category_id |
| ----------: |
|           1 |
|           2 |
|           3 |

-- a functionally-equivalent, simpler query for this
select sc.*
from story_category sc
where not exists (select 1 from category c where c.id = sc.category_id)

| category_id |
| ----------: |
|           1 |
|           2 |
|           3 |

-- the delete query
delete from story_category
where not exists (select 1 from category c where c.id = story_category.category_id);

-- outcome
select * from story_category;

| category_id |
| ----------: |
|           4 |
|           5 |