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?
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
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?
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'
)
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'
)
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?
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.