I have to delete rows from guide_category
that have no relation with guide
table (dead relations).
Here is what I want to do, but it of course does not work.
DELETE FROM guide_category AS pgc
WHERE pgc.id_guide_category IN (SELECT id_guide_category
FROM guide_category AS gc
LEFT JOIN guide AS g ON g.id_guide = gc.id_guide
WHERE g.title IS NULL)
Error:
You can't specify target table 'guide_category' for update in FROM clause
This question is related to
mysql
sql
delete-row
sql-delete
mysql-error-1093
How about:
DELETE guide_category
WHERE id_guide_category IN (
SELECT id_guide_category
FROM guide_category AS gc
LEFT JOIN guide AS g
ON g.id_guide = gc.id_guide
WHERE g.title IS NULL
)
Try this sample SQL scripts for easy understanding,
CREATE TABLE TABLE1 (REFNO VARCHAR(10))
CREATE TABLE TABLE2 (REFNO VARCHAR(10))
--TRUNCATE TABLE TABLE1
--TRUNCATE TABLE TABLE2
INSERT INTO TABLE1 SELECT 'TEST_NAME'
INSERT INTO TABLE1 SELECT 'KUMAR'
INSERT INTO TABLE1 SELECT 'SIVA'
INSERT INTO TABLE1 SELECT 'SUSHANT'
INSERT INTO TABLE2 SELECT 'KUMAR'
INSERT INTO TABLE2 SELECT 'SIVA'
INSERT INTO TABLE2 SELECT 'SUSHANT'
SELECT * FROM TABLE1
SELECT * FROM TABLE2
DELETE T1 FROM TABLE1 T1 JOIN TABLE2 T2 ON T1.REFNO = T2.REFNO
Your case is:
DELETE pgc
FROM guide_category pgc
LEFT JOIN guide g
ON g.id_guide = gc.id_guide
WHERE g.id_guide IS NULL
I think, from your description, the following would suffice:
DELETE FROM guide_category
WHERE id_guide NOT IN (SELECT id_guide FROM guide)
I assume, that there are no referential integrity constraints on the tables involved, are there?
Source: Stackoverflow.com