SQL statement I wrote (delete category, update number)

Today, I wrote a complex SQL statement. The result shows that it does not meet the project requirements, but the syntax is worth learning.

1. Declare @ delid int -- the row to be deleted
2. Declare @ CID int
4. Set @ delid = '000000'
6. Begin transaction T1
8. Delete from ttest where Unit No. = @ delid
10. Declare mycur cursor for select ID from ttest where convert (INT, unit number)> convert (INT, @ delid)
11. Open mycur
12. Fetch next from mycur into @ CID -- similar to datareader. Read (), move down
13. While (@ fetch_status = 0) -- similar to datareader. Read ()
14. Begin -- processing logic here
15. Update ttest set unit number = '00' + convert (varchar (20), convert (INT, unit number)-1) Where id = @ CID
16. Fetch next from mycur into @ CID -- start a new round of loop. If this is missing, it becomes an endless loop.
17. End
18. Close mycur
19. Deallocate mycur
21. Commit

My data table is ttest.

Id int Unit No. varchar (20)

2 001001

3 001002

4 001003

5 001004

6 001005

7 001006

The business logic I want to implement is:

If you delete a row, such as 001002

The updated row number is greater than 001002, that is, the number is reduced by 1.

That is, keep the numbers neatly.

 

The problem I didn't consider was that when I updated other rows, other rows were associated with many tables.

Therefore, other rows cannot be updated during deletion.

In addition, find the smallest number to be deleted and add it.