Search code examples
sqlitelimit

SQLITE update, limit, case

enter image description herei want to implement a parking lot application so there is a garage with 5 or more parking lots when the driver parks his car the next free slot in the garage should be assigned to him.

so i have a table garage with 5 or more slots i have the problem when i update update garage set free = 0, set car = 1 (car_id) then all free slots are update how can i limit only to update the first free row?

Can anyone help me

thanks in advance (false) Thanks i have garage table with levels and slots in each level when i park a car i want to update just the free slots

table garage

Solution

  • You'll need to specify which record(s) you want to update, or the database will update all of them. If you're looking to update one record specifically, you might want to reference its PRIMARY KEY with a WHERE clause.

    If you haven't assigned a PRIMARY KEY for your table, you could use SQLite's rowid column. Something like this:

    SELECT rowid FROM garage WHERE free = 1;

    Then, supposing you identify spot 5 is free for car 42:

    UPDATE garage SET free = 0, car = 42 WHERE rowid = 5;

    If you prefer, you can combine those two steps into a single query:

    UPDATE garage SET free=0, car=42 WHERE rowid IN (SELECT rowid FROM garage WHERE free=1 LIMIT 1);