Help with Delete Query

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
millsy007
Forum Commoner
Posts: 78
Joined: Wed Jul 02, 2008 7:00 pm

Help with Delete Query

Post by millsy007 »

I have a delete query that makes sense (to me at least lol), It is deleting the record of a passenger who has cancelled a coach journey, it is passing in their name and the coach they are on and deleting the record that matches, that is the plan anyway, however it gives the error:

"You can't specify target table 'passengers' for update in FROM clause" - But I need to use that table to delete from and query in order to find the right record to delete?

SQL:

Code: Select all

         DELETE  FROM passengers 
        WHERE   EXISTS (
        SELECT  passengers.id
        FROM    passengers, shuttle, journey
        WHERE   coach.id = journey.shuttle_id
        AND   journey.id = passenger.journey_id
        AND   coach.id = '$id'
        AND   passenger.passenger_name = '$name')
I have a feeling I am making a silly mistake but my syntax seems to match the examples I look up so would appreciate any advice. Thanks
User avatar
SpecialK
Forum Commoner
Posts: 96
Joined: Mon Sep 18, 2006 3:49 pm

Re: Help with Delete Query

Post by SpecialK »

I would think it's in your subselection

Code: Select all

SELECT  passengers.id
        FROM    passengers, shuttle, journey
        WHERE   coach.id = journey.shuttle_id
        AND   journey.id = passenger.journey_id
        AND   coach.id = '$id'
        AND   passenger.passenger_name = '$name'
You are joining 3 tables, passengers, shuttle and journey. Journey and Passenger are joined on jouney id. Where is shuttle joined in there? I don't see a table called coach in there that is being used in this selection. Perhaps one of these is labelled incorrectly?
millsy007
Forum Commoner
Posts: 78
Joined: Wed Jul 02, 2008 7:00 pm

Re: Help with Delete Query

Post by millsy007 »

Hi Thanks for the spot :D

I corrected that and now changed it to:

SELECT *
FROM passengers
WHERE passengers.id
IN (

SELECT passengers.id
FROM passengers, shuttle, journey
WHERE shuttle.id = journey.shuttle_id
AND journey.id = passengers.journey_id
AND shuttle.id = '1'
AND passengers.passenger_name = 'bobby'
)

And it seems to be doing what I need
User avatar
Benjamin
Site Administrator
Posts: 6935
Joined: Sun May 19, 2002 10:24 pm

Re: Help with Delete Query

Post by Benjamin »

I know that's not much code but please use the code tags. There's actually an sql parser built in, activated by using =sql.

Code: Select all

 
SELECT *
FROM passengers
WHERE passengers.id
IN (
 
SELECT passengers.id
FROM passengers, shuttle, journey
WHERE shuttle.id = journey.shuttle_id
AND journey.id = passengers.journey_id
AND shuttle.id = '1'
AND passengers.passenger_name = 'bobby'
)
 
millsy007
Forum Commoner
Posts: 78
Joined: Wed Jul 02, 2008 7:00 pm

Re: Help with Delete Query

Post by millsy007 »

Okay thanks, do you mean surround the sql in the tags [=sql][/sql] ?
User avatar
Benjamin
Site Administrator
Posts: 6935
Joined: Sun May 19, 2002 10:24 pm

Re: Help with Delete Query

Post by Benjamin »

Code: Select all

[/code ]

Without spaces.
millsy007
Forum Commoner
Posts: 78
Joined: Wed Jul 02, 2008 7:00 pm

Re: Help with Delete Query

Post by millsy007 »

thanks, unfortunately my SQL works when I test it using the SELECT but as soon as I change it to DELETE I get:

#1093 - You can't specify target table 'passengers' for update in FROM clause

So I have

Code: Select all

DELETE  FROM passengers WHERE passengers.id IN (
SELECT passengers.id
FROM passengers, shuttle, journey
WHERE shuttle.id = journey.shuttle_id
AND journey.id = passengers.journey_id
AND shuttle.id = '1'
AND passengers.passenger_name = 'bobby'
)
[/code ]

Should I split my query up?
User avatar
SpecialK
Forum Commoner
Posts: 96
Joined: Mon Sep 18, 2006 3:49 pm

Re: Help with Delete Query

Post by SpecialK »

Looking more at the query, can it be done like this?

Code: Select all

DELETE FROM passengers WHERE passengers.passenger_name = 'bobby'
Depending on your schema, but this will effectively delete the same thing as your query as you are grabbing a passenger ID and deleteing it. It won't let you delete from passengers because you are selecting the data you are deleting. I've never seen it done this way, but there are many ways to do SQL and end up with the same result.
millsy007
Forum Commoner
Posts: 78
Joined: Wed Jul 02, 2008 7:00 pm

Re: Help with Delete Query

Post by millsy007 »

I needed the shuttle criteria so ended with:

Code: Select all

    
        DELETE  passengers
    FROM    passengers 
               INNER JOIN journey
               ON passengers.journey_id = journey.id
               INNER JOIN shuttle
               ON shuttle.id = journey.shuttle_id
    WHERE  shuttle.id = '$id'
    AND     passengers.passenger_name = '$name'
 
Post Reply