Update Query Error

[millsy007]

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

[pritaeas]

If you have a look at the manual http://dev.mysql.com/doc/refman/5.0/en/update.html you can see that the table references (joins) should be _before_ the SET

[nav33n]

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

[millsy007]

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