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

how about this query hope it helps

DELETE FROM story_category LEFT JOIN (SELECT category.id FROM category) cat ON story_category.id = cat.id WHERE cat.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));

try this

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

[NexusRex](

[To see links please register here]

) provided a [very good solution](

[To see links please register here]

) 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_](

[To see links please register here]

) 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.

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:

```sql
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.<br/>[Documentation][1]

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


[1]:

[To see links please register here]

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 `JOIN`s 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](

[To see links please register here]

)**:

-- 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);

<pre>
| category_id |
| ----------: |
| 1 |
| 2 |
| 3 |
</pre>

-- 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)


<pre>
| category_id |
| ----------: |
| 1 |
| 2 |
| 3 |
</pre>

-- 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;

<pre>
| category_id |
| ----------: |
| 4 |
| 5 |
</pre>

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);