SQL how to increase or decrease one for a int column in one command

Question

I have an Orders table which has a Quantity column  During check in or check out  we need to update that Quantity column by one  Is there a way to do this in one action or we have to get the existing value and then add or minus one on top of it   Another question is when we insert a new row  do we need to check if same data existing then insert if not  which is two steps  or is there a better way to do this   thanks

User · Answer

If my understanding is correct  updates should be pretty simple  I would just do the following   UPDATE TABLE SET QUANTITY   QUANTITY   1 and UPDATE TABLE SET QUANTITY   QUANTITY - 1 where QUANTITY  gt  0   You may need additional filters to just update a single row instead of all the rows   For inserts  you can cache some unique id related to your record locally and check against this cache and decide whether to insert or not  The alternative approach is to always insert and check for PK violation error and ignore since this is a redundant insert

User · Answer

The single-step answer to the first question is to use something like   update TBL set CLM   CLM   1 where key    KEY    That s very much a single-instruction way of doing it   As for the second question  you shouldn t need to resort to DBMS-specific SQL gymnastics  like UPSERT  to get the result you want  There s a standard method to do update-or-insert that doesn t require a specific DBMS   try      insert into TBL  key val  values   xyz  0  catch      do nothing update TBL set val   val   1 where key    xyz    That is  you try to do the creation first  If it s already there  ignore the error  Otherwise you create it with a 0 value   Then do the update which will work correctly whether or not    the row originally existed  someone updated it between your insert and update    It s not a single instruction and yet  surprisingly enough  it s how we ve been doing it successfully for a long long time

User · Answer

to answer the second   make the column unique and catch the exception if it s set to the same value

User · Answer

To answer the first   UPDATE Orders SET Quantity   Quantity   1 WHERE       To answer the second   There are several ways to do this   Since you did not specify a database  I will assume MySQL    INSERT INTO table SET x 1  y 2 ON DUPLICATE KEY UPDATE x x 1  y y 2 REPLACE INTO table SET x 1  y 2   They both can handle your question   However  the first syntax allows for more flexibility to update the record rather than just replace it  as the second one does    Keep in mind that for both to exist  there has to be a UNIQUE key defined

User · Answer

UPDATE Orders Order SET Order Quantity    Order Quantity - 1 WHERE SomeCondition Order    As far as I know there is no build-in support for INSERT-OR-UPDATE in SQL  I suggest to create a stored procedure or use a conditional query to achiev this  Here you can find a collection of solutions for different databases

User · Answer

dotjoe It is cheaper to update and check   rowcount  do an insert after then fact    Exceptions are expensive   amp  amp  updates are more frequent  Suggestion  If you want to be uber performant in your DAL  make the front end pass in a unique ID for the row to be updated  if null insert   The DALs should be CRUD  and not need to worry about being stateless    If you make it stateless  With good indexes  you will not see a diff with the following SQL vs 1 statement  IF  select top 1   form x where PK  ID      Insert else      update

[database] SQL how to increase or decrease one for a int column in one command

Examples related to database