Skip to content
Advertisement

SQLite3 Update a row based on a select query in another table

Basically I am trying to find a way to update the vehicle’s booking status associated with the client_drivers_license_number after its matched to a variable I will input on my laravel project (123456789 below). I am not sure how I can make an update with values matching from mulitple tables so my best attempt is below so I can explain the idea of what I want to do. This is all in sqlite3.

UPDATE SET vehicle.is_booked = 0
FROM booking, vehicle
WHERE booking.vehicle_rego = vehicle.rego and booking.client_drivers_license_number = 123456789;

Thank you for any and all help.

Advertisement

Answer

You could write this using exists:

UPDATE vehicle
    SET is_booked = 0
    WHERE EXISTS (SELECT 1
                  FROM booking b
                  WHERE b.vehicle_rego = vehicle.rego AND  
                        b.client_drivers_license_number = 123456789
                 );

It seems odd that you would set is_booked = 0 when a booking is available. But then again, it seems odd that there are no date or times in this query at all.

User contributions licensed under: CC BY-SA
3 People found this is helpful
Advertisement