0

I have this query:

"UPDATE Bookings SET total=0 
FROM Bookings JOIN Vehicles on Bookings.CustomerID=Vehicles.CustomerID
WHERE hasWarranty='yes'";

Every time I run it, it says Syntax error near 'from' and I don't know why.

I basically want to check: does the vehicle associated with the booking have warranty - if it does, set total to 0. I want to update the value of one table if another value in another table equals x.

Benjamin Hodgson
  • 42,952
  • 15
  • 108
  • 157
u.1234
  • 23
  • 1
  • 1
  • 5

2 Answers2

1

Sorry, but there is no FROM in UPDATE clauses. You have to completely rewrite this query.

I guess you want to do something like this:

UPDATE Bookings SET total=0 
WHERE (SELECT hasWarranty FROM Vehicles WHERE Vehicles.CustomerID = Bookings.CustomerID) = 'yes'
marcus.kreusch
  • 648
  • 5
  • 15
  • just tried this and no syntax error anymore but if warranty is equal to no its still updates it to 0 – u.1234 Mar 24 '15 at 17:01
  • You're welcome! Just to make sure you don't run into issues: You might want to think about the question if it still works in case your application supports several Vehicles for one CustomerID because in this case a random Vehicle is chosen for comparison. – marcus.kreusch Mar 24 '15 at 17:15
0

You do not need to use the FROM keyword when doing an update

UPDATE Bookings AS b JOIN Vehicles AS v ON b.CustomerId = v.CustomerID SET b.total = 0 WHERE v.hasWarranty = 'yes';
Joseph Crawford
  • 1,470
  • 1
  • 15
  • 29
  • You missed the `JOIN`. – Mike Mar 24 '15 at 16:54
  • I have never done a join in an update, actually thought it would lead to invalid syntax as well. I would have to play with this to put the join in place but I also see no reason for a join since hasWarranty seems to be in the Booking table. – Joseph Crawford Mar 24 '15 at 16:55
  • The following query will work as well but there is no need to join tables unless you are using a value from the joined table, in this case it does not appear to be using a reference from the Vehicles table. If hasWarranty is in the Vehicles table then you would reference it by v.hasWarranty rather than b.hasWarranty. UPDATE Bookings AS b JOIN Vehicles AS v ON b.CustomerId = v.CustomerID SET b.total = 0 WHERE b.hasWarranty = 'yes'; – Joseph Crawford Mar 24 '15 at 16:58
  • It would seem to me that `hasWarranty` would make much more sense to be on a vehicle, not a booking. See [this question](http://stackoverflow.com/a/4192849/811240) about joins on delete. Updates should work the same way. – Mike Mar 24 '15 at 16:58
  • Learn something new every day :) Thanks for pointing that out Mike. I've just never had a reason to cross join tables on an update :) – Joseph Crawford Mar 24 '15 at 17:00
  • ok thought it was working but it sets total to 0 for all warranties not just where warranty is equal to yes. hasWarranty is in a Vehicles table not Bookings;) – u.1234 Mar 24 '15 at 17:46
  • Hmm I just realized that this question is sqlite. For some reason I had MySQL in my mind, so you would have to test it to see if it works in sqlite as well. – Mike Mar 24 '15 at 17:46
  • doesn't work in sqllite as updates all to 0, when i use josephs update it again gives me a syntax error. – u.1234 Mar 24 '15 at 17:50
  • it doesn't update the first row where hasWarranty is equal to no but updates the rest of the rows – u.1234 Mar 24 '15 at 18:13
  • @u.1234 What table are `hasWarranty` and `CustomerID` on? – Mike Mar 24 '15 at 18:56
  • hasWarranty is in Vehicles and CustomerID is in Bookings – u.1234 Mar 24 '15 at 18:58
  • it doesn't even look at the has warranty part just updates all values – u.1234 Mar 24 '15 at 18:58
  • @u.1234 If you look at this query, you will notice that "`Vehicles`" does not even appear anywhere. If you want the update to be dependent of a value in the `Vehicles` table, you have to do a `JOIN`. It's not going to be able to get the value if you don't. – Mike Mar 24 '15 at 19:00
  • i was talking about the solution you submitted it updates them all doesn't even look at hasWarranty – u.1234 Mar 24 '15 at 19:21
  • oh sorry not you who submitted someone else! – u.1234 Mar 24 '15 at 19:21