Page 1 of 1

Help with Delete Query

Posted: Mon Feb 16, 2009 11:18 am
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

Re: Help with Delete Query

Posted: Mon Feb 16, 2009 3:22 pm
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?

Re: Help with Delete Query

Posted: Mon Feb 16, 2009 3:49 pm
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

Re: Help with Delete Query

Posted: Mon Feb 16, 2009 4:05 pm
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'
)
 

Re: Help with Delete Query

Posted: Mon Feb 16, 2009 4:12 pm
by millsy007
Okay thanks, do you mean surround the sql in the tags [=sql][/sql] ?

Re: Help with Delete Query

Posted: Mon Feb 16, 2009 4:18 pm
by Benjamin

Code: Select all

[/code ]

Without spaces.

Re: Help with Delete Query

Posted: Mon Feb 16, 2009 4:28 pm
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?

Re: Help with Delete Query

Posted: Tue Feb 17, 2009 8:54 am
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.

Re: Help with Delete Query

Posted: Tue Feb 17, 2009 1:31 pm
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'