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

Question

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.

1. INSERT INTO table SET x=1, y=2 ON DUPLICATE KEY UPDATE x=x+1, y=y+2
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...

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.

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.

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.