根据同一表中的字段更新oldID字段 [英] update oldID field based on fields in the same table

问题描述

我需要以下查询的帮助.

I need help with the following query.

create table #table1
(id int not null primary key identity,
customer_name varchar(25),
usage float,
oldID int null
)


insert into #table1 values('ABC',46.5,null)
insert into #table1 values('ABC',46.5,null)
insert into #table1 values('DEF',36.8,null)
insert into #table1 values('XYZ',50.1,null)
insert into #table1 values('DEF',36.8,null)
insert into #table1 values('XYZ',50.1,null)

select * from #table1

我希望这样更新我的表

id          customer_name             usage                  oldID
----------- ------------------------- ---------------------- -----------
1           ABC                       46.5                   NULL
2           ABC                       46.5                   1
3           DEF                       36.8                   NULL
4           XYZ                       50.1                   NULL
5           DEF                       36.8                   3
6           XYZ                       50.1                   4

1. 两个具有相同名称和用法的记录意味着以后的记录已更新.
2. 在新记录中，oldID字段应指向其旧记录(ID).

尽管在我的实际表中，我可能会使用一堆日期字段，但这暂时对我有帮助.

Although in my actual table, I have a bunch of date fields which I probably can use but this would help me for now.

推荐答案

使用cte，不使用子查询，仅更新具有多行的客户:

With cte, without subquerys, updating only customers with several rows:

with cte as (
   select customer_name, min( id ) as id
   from #table1
   group by customer_name
   having count(*) > 1
)
update #table1 
 set oldID = cte.id
 from cte 
 where #table1.customer_name = cte.customer_name
 and #table1.id != cte.id 

这篇关于根据同一表中的字段更新oldID字段的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！