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

Question

I have an order table that has a Quantity column. During verification or verification, we need to update the "Quantity" column by one. Is there a way to do this in one action, or should we get the existing value and then add or minus one on top of it?

Another question: when we insert a new row, do we need to check if the same data exists, and then insert, if not, what are two steps, or is there a better way to do this?

thank,

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.

• 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

Both of them can solve your question. However, the first syntax allows you to more flexibly update the record, rather than just replacing it (as the second does).

Keep in mind that in order for both to exist, a UNIQUE key must be defined ...

Answer 2

The one-step answer to the first question is to use something like:

 update TBL set CLM = CLM + 1 where key = 'KEY' 

This is a very easy way to do this.

As in the second question, you do not need to resort to special SQL gymnastics, such as a DBMS (for example, UPSERT ), to get the desired result. There is a standard method for updating or inserting that does not 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 are first trying to make a creation. If it already exists, ignore the error. Otherwise, you will create it with a value of 0.

Then perform an update that will work correctly or not:

• the string originally existed.
• someone updated it between your insert and update.

This is not one instruction, and, nevertheless, it is amazing how we have been doing this successfully for a long time.

Answer 3

 UPDATE Orders Order SET Order.Quantity = Order.Quantity - 1 WHERE SomeCondition(Order) 

As far as I know, native INSERT-OR-UPDATE support in SQL is not supported. I suggest creating a stored procedure or using a conditional query to achieve this. Here you can find a set of solutions for different databases.

Answer 4

If my understanding is correct, the updates should be fairly 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 simply update one row instead of all rows.

For insertions, you can cache the unique identifier associated with your record locally and check this cache and decide whether to insert it or not. An alternative approach is to always insert and check a PK violation error and ignore it, since this is an excessive insertion.

Answer 5

to answer the second:

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

Answer 6

@dotjoe Cheaper to update and check @@ rowcount, do an insert after this fact.

Exceptions are costly and updates are more common

Sentence. If you want to be an uber executor in your DAL, make the front end in a unique identifier for the line to be updated if the insert is zero.

DAL must be CRUD, and no need to worry about being stateless.

If you make it stagnant, with good indices, you will not see diff with the following SQL statement vs 1. IF (select the top 1 * form x, where PK = @ID) Paste another update