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