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

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,

@dotjoe It is cheaper to update and check @@rowcount, do an insert after then fact.

Exceptions are expensive && 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

to answer the second:

make the column unique and catch the exception if it's set to the same value.

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.

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.

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 > 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.