我正在使用单个插入语句将一批记录添加到表中.我希望为每个新批次分配递增的数字,但每次都从1开始.
I am adding batches of records to a table using a single insert statement. I want each new batch to be allocated incrementing numbers, but starting from 1 each time.
所以,如果我有
Batch Name IncementingValue 1 Joe 1 1 Pete 2 1 Andy 3 2 Sue 1 2 Mike 2 2 Steve 3然后我添加两条记录(使用单个插入语句):
and I then add two records (using a single insert statement) :
3 Dave 3 Paul如何针对此表运行更新语句,以使Dave设置为1,Paul设置为2.我不想使用游标.
How can I run an update statement against this table so that Dave will be set to 1 and Paul to 2. I don't want to use a cursor.
推荐答案排名函数ROW_NUMBER应该可以满足您的需求.您没有提及有关如何分配序列号的任何特定规则,因此我在这里使用名称来完成此操作:
The ranking function ROW_NUMBER should do what you need. You didn't mention any specific rules about how the sequence number should be allocated, so I've done it here using the name:
INSERT targetTable(Batch,Name,IncementingValue) SELECT BatchId, Name, ROW_NUMBER() OVER (ORDER BY Name) FROM sourceTable更多推荐
SQL更新记录,每次递增值从1开始
发布评论