By hsz


2009-12-30 15:27:50 8 Comments

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

4 comments

@user2384628 2013-05-15 07:03:52

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

@NikosKeyz 2016-02-27 21:50:03

now THAT's the answer. simple and solid. well done sir

@sstn 2016-04-05 11:05:46

@NikosKeyz That's exactly the same answer as the accepted answer, isn't it?

@NikosKeyz 2016-04-09 20:35:09

@sstn no. the accepted answer didnt work for me. This is the best answer for me.

@Dirk 2009-12-30 15:35:19

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?

@Toumi 2016-10-12 12:51:12

this will be a little bit slow as it will execute a (SELECT id_guide FROM guide) for every entry

@Dirk 2016-10-12 16:53:45

That actually depends, @Toumi. The query planner/optimizer can do a lot behind the scenes here.

@Quassnoi 2009-12-30 15:31:09

Due to the locking implementation issues, MySQL does not allow referencing the affected table with DELETE or UPDATE.

You need to make a JOIN here instead:

DELETE  gc.*
FROM    guide_category AS gc 
LEFT JOIN
        guide AS g 
ON      g.id_guide = gc.id_guide
WHERE   g.title IS NULL

or just use a NOT IN:

DELETE  
FROM    guide_category AS gc 
WHERE   id_guide NOT IN
        (
        SELECT  id_guide
        FROM    guide
        )

@Gezim 2013-10-13 08:19:53

If you're using TSQL, Use DELETE gc instead of DELETE gc.*

@Daniel W. 2014-06-03 09:14:12

For MySQL > 5.0 it's also DELETE gc instead of gc.*

@duleshi 2016-07-19 09:19:35

But which one is faster?

@Quassnoi 2016-07-19 09:27:44

@duleshi: when properly indexed, the same.

@Michael Piefel 2017-07-20 08:15:23

@duleshi: Depending on your database, one may be dramatically faster than the other. In my experience, the LEFT JOIN with IS NULL is fast, the NOT IN is slow. That was a Postgres database, even with all indexes in place.

@Quassnoi 2017-07-20 09:01:12

@MichaelPiefel: LEFT JOIN with NOT NULL and NOT IN are, generally speaking, semantically different.

@Philippe Leybaert 2009-12-30 15:30:47

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

  )

@siride 2014-08-17 14:38:14

That's the original query and has the exact same problem of referencing the original table in a derived table expression.

Related Questions

Sponsored Content

37 Answered Questions

29 Answered Questions

[SOLVED] Finding duplicate values in a SQL table

  • 2010-04-07 18:17:29
  • Alex
  • 2570361 View
  • 1735 Score
  • 29 Answer
  • Tags:   sql duplicates

15 Answered Questions

[SOLVED] How to Delete using INNER JOIN with SQL Server?

7 Answered Questions

15 Answered Questions

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

11 Answered Questions

[SOLVED] Can I concatenate multiple MySQL rows into one field?

27 Answered Questions

[SOLVED] SQL select only rows with max value on a column

5 Answered Questions

[SOLVED] Deleting rows with MySQL LEFT JOIN

11 Answered Questions

[SOLVED] SQL Server: How to Join to first row

6 Answered Questions

[SOLVED] SQL query return data from multiple tables

  • 2012-09-18 11:11:39
  • Fluffeh
  • 771634 View
  • 420 Score
  • 6 Answer
  • Tags:   mysql sql select

Sponsored Content