Find an update insert statement online, please everyone!

flyinghorse

Table A

Code Name quantity
001 TV 100
002 Refrigerator 200

Desired effect:
Code Name quantity
001 TV 80
002 Refrigerator 200
001 TV 20
Note: If I update the number of 001 in the interface to 20; at this time, I insert a new one and change the original number to 80; if I update it, the new number exceeds 80 and it will not operate.
Please give an example

riki_w

The expression is not very clear.It seems that your question should not be considered from the SQL statement.

sanshuimei

I want to ask you, if I update again this time, and this time I want to update 10, should I reduce it in 80 or decrease it in 20? ?

terrorlin

I think what he meant is that every update is split from the original record
How about this:
1. Find the most original one
2. The input parameter is compared with the number of 1, and the update is allowed if it is less than or equal to; otherwise, exit
3. Insert a record with the parameter of 2
4. Correspondingly reduce the number of original records

terrorlin

-Find out the earliest inserted record in each type
(select Code,min(rowid) rid from A group by Code)