4 replies to this topic

[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

[Orjan]

Are you really sure the query specified above is the same as the error comes from?

because the error message states that you are doing an inner join on the journey table and I can't see that join.

[WingedPanther]

I don't know that you can do joins in an update statement.

[millsy007]

Apologies, actual error is:
#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 passengers p On p.journey_id = j.id I' at line 3

If I cant use a join like this what would be a suitable alternative?

[Orjan]

when joining an update, you need a from...

Example:

UPDATE d

SET SomeColumn = newvalue

FROM tabled d

INNER JOIN tablep p

ON d.id = p.id

WHERE d.id = 5