Hi, I would appreciate some help, I cannot get my query to work:

UPDATE     journey j
SET          j.occupancy=j.occupancy-1
               Inner Join passengers p
               On p.journey_id = j.id
               Inner Join shuttle s
               On s.id = j.shuttle_id
WHERE      s.id = '1'
AND          p.passenger_name = 'bill gates'

It is used when a cancelation is made to reduce the occupancy for that journey to reflect that passenger cancelling, my table/field names are okay and my links are right but I get the error:

#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'Inner Join journey On passengers.journey_id = journey.id' at line 3

Not sure if relevant but I have MySQL client version: 4.1.22 - Server version: 5.0.51a-community

9 Years
Discussion Span
Last Post by millsy007
update journey j, passengers p, shuttle s set j.occupancy=j.occupancy - 1 where p.journey_id = j.id and s.id = j.shuttle_id and s.id = 1 and p.passenger_name='bill gates'

wouldn't this work ? Many people at my workplace avoid joins (they say its comparatively slower than normal queries).

Votes + Comments

Thanks for the replies, my syntax was wrong, opps. Yes I have changed it to that and problems sorted. Cheers!

This topic has been dead for over six months. Start a new discussion instead.
Have something to contribute to this discussion? Please be thoughtful, detailed and courteous, and be sure to adhere to our posting rules.