Update multiple records of multiple records with SQL

xiaoxiao2021-03-06  110

Today, a problem is encountered, and the corresponding adjustment is adjusted to multiple records when updating a record.

Table T1

ID Categories Name Sortnum --------------------------------------- 1 1 CHN 12 1 USA 23 1 RSA 34 2 JPN 1

Taking this table as an example, when the categories of the update ID 2 and 3 is 2, the number of sort numbers is updated. SORTNUM

According to the simplest and direct ideas, you will write a few update statements to update several records. But actually there can be a simpler method:

Declare @s tentSelect @s = count (*) from t1 where categories = 2Update t1set categories = 2, sortnum = @S, @s = @S 1where id in (2, 3)

Pay attention to the shape of the font, through this accumulated manner, you get a self-incremental value, and then assign it to Sortnum.

In addition, here only gives some part of the problem that I started to start, and finally need to adjust the sort number in the original categories (SortNum, which recorded by categories = 1), similar to the SQL above, I don't have to Take again :)

转载请注明原文地址:https://www.9cbs.com/read-126368.html

New Post(0)