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,

Answer 1

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

Answer 2

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.

Answer 3

to answer the second:

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

Answer 4

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.

Answer 5

@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

Answer 6

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.