I have a table story_category
in my database with corrupt entries. The next query returns the corrupt 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);
I tried to delete them executing:
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 get the next error:
#1093 - You can't specify target table 'story_category' for update in FROM clause
How can I overcome this?
This question is related to
mysql
subquery
sql-delete
mysql-error-1093
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) :
UPDATE My_Table
SET Priority=Priority + 1
WHERE Priority >= 1
AND (SELECT TRUE FROM (SELECT * FROM My_Table WHERE Priority=1 LIMIT 1) as t);
I know it's a bit ugly but it does works fine.
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.
Do this:
DELETE FROM story_category
WHERE category_id NOT IN (
SELECT DISTINCT category.id
FROM category);
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);
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
)
try this
DELETE FROM story_category
WHERE category_id NOT IN (
SELECT DISTINCT category.id
FROM (SELECT * FROM STORY_CATEGORY) sc;
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));
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.
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.
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.
-- 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 |
Recently i had to update records in the same table i did it like below:
UPDATE skills AS s, (SELECT id FROM skills WHERE type = 'Programming') AS p
SET s.type = 'Development'
WHERE s.id = p.id;
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
Try to save result of Select statement in separate variable and then use that for delete query.
If you can't do
UPDATE table SET a=value WHERE x IN
(SELECT x FROM table WHERE condition);
because it is the same table, you can trick and do :
UPDATE table SET a=value WHERE x IN
(SELECT * FROM (SELECT x FROM table WHERE condition) as t)
[update or delete or whatever]
According to the Mysql UPDATE Syntax linked by @CheekySoft, it says right at the bottom.
Currently, you cannot update a table and select from the same table in a subquery.
I guess you are deleting from store_category while still selecting from it in the union.
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.
Source: Stackoverflow.com