Welcome to OStack Knowledge Sharing Community for programmer and developer-Open, Learning and Share
Welcome To Ask or Share your Answers For Others

Categories

0 votes
733 views
in Technique[技术] by (71.8m points)

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

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,

See Question&Answers more detail:os

与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
Welcome To Ask or Share your Answers For Others

1 Answer

0 votes
by (71.8m points)

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


与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
Welcome to OStack Knowledge Sharing Community for programmer and developer-Open, Learning and Share
Click Here to Ask a Question

2.1m questions

2.1m answers

60 comments

57.0k users

...