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,
This question is related to
database
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+2REPLACE INTO table SET x=1, y=2They 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...
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.
to answer the second:
make the column unique and catch the exception if it's set to the same value.
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:
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.
@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
Source: Stackoverflow.com