大佬教程收集整理的这篇文章主要介绍了需要更新 SQL Server 2016 中的完整表,大佬教程大佬觉得挺不错的,现在分享给大家,也给大家做个参考。
我想在有重复条目的地方用 D-
ntext
值更新整个表。我还试图根据 ExternalemployeEID
获取所有重复条目,但只想更新基于旧 @H_494_3@modifIEddate 的那些历史记录。
目前,我正在尝试基于记录列表:
create table [employee]
(
[RegionalemployeEID] [int] IDENTITY(1,1) NOT NulL,[ntext] [varchar](20) NOT NULL,[ExternalemployeEID] [varchar](50) NulL,[employeeClass] [varchar](5) NulL,[CreatedDate] [datetiR_461_11845@e2](7) NulL,[ModifIEdDate] [datetiR_461_11845@e2](7) NulL,CONSTraiNT [PK_employee]
PRIMary KEY CLUSTERED ([RegionalemployeEID] ASC)
WITH (PAD_INDEX = OFF,STATISTICS_norECOmpuTE = OFF,IGnorE_DUP_KEY = OFF,ALLOW_ROW_LOCKS = ON,ALLOW_PAGE_LOCKS = ON) ON [PRIMary]
) ON [PRIMary]
INSERT INTO [employee]([ntext],[ExternalemployeEID],[employeeClass],[CreatedDate],[ModifIEdDate]) VALUES ('DEF567','345','I','2021-03-07','2020-03-08');
INSERT INTO [employee]([ntext],'2021-03-08','2020-03-09');
INSERT INTO [employee]([ntext],'P','2021-03-09','2020-03-10');
INSERT INTO [employee]([ntext],'2021-03-010','2020-03-11');
INSERT INTO [employee]([ntext],[ModifIEdDate]) VALUES ('CDE234','6789','O','2021-03-03',null);
INSERT INTO [employee]([ntext],'2021-03-04','2021-03-05',[ModifIEdDate]) VALUES ('ABC123','12345','2021-03-01','2021-03-02','2020-03-02');
INSERT INTO [employee]([ntext],'2020-03-03');
INSERT INTO [employee]([ntext],'2020-03-04);
--Get the duplicate ExternalemployeEID
SELECT ntext,ExternalemployeEID
INTO #ETemp
FROM employee
WHERE LEN(ExternalemployeEID) > 1
AND (employeeClass = 'I' OR employeeClass = 'O' OR employeeClass = 'P')
GROUP BY ntext,ExternalemployeEID
HAVING COUNT(ExternalemployeEID) > 1
--Find the ExternalemployeEID need to be soft-deleted based on RegionalemployeEID
SELECT *
FROM
(SELECT
RegionalemployeEID,ROW_numbER() OVER (PARTITION BY ntext,ExternalemployeEID ORDER BY ModifIEdDate desc) AS Rownumber
FROM
employee E
WHERE
ntext IN (SELECT ntext FROM #ETemp)
AND ExternalemployeEID IN (SELECT ExternalemployeEID FROM #ETemp)
AND (employeeClass = 'I' OR employeeClass = 'O' OR employeeClass = 'P')
) AS T
WHERE
T.Rownumber > 1
预期结果应该如下所示,因为最后一个是最近修改过的记录,所以不要改变那个。
D-DEF567,345,I,2021-03-07,2020-03-08
D-DEF567,2021-03-08,2020-03-09
D-DEF567,P,2021-03-09,2020-03-10
DEF567,2021-03-010,2020-03-11
CDE234,6789,O,2021-03-03,NulL
CDE234,2021-03-04,2021-03-05,NulL
ABC123,12345,2021-03-01,NulL
D-ABC123,'I,2021-03-02,2020-03-02
D-ABC123',2020-03-03
ABC123',NulL
ABC123,2020-03-04
同样的,如果有更多基于ExternalemployeEID
的重复记录,我需要更新记录列表。
使用 row_number()
来标识“历史”记录并在 @H_494_3@modifiedDate is not null 时更新
; with emp as
(
select *,rn = row_number() over (partition by [ExternalemployeEID]
order by [ModifiedDate] desc)
from employee e
)
update emp
set ntext = case when rn > 1 and ModifiedDate is not null
then 'D-' + ntext
else ntext
end
from emp
以上是大佬教程为你收集整理的需要更新 SQL Server 2016 中的完整表全部内容,希望文章能够帮你解决需要更新 SQL Server 2016 中的完整表所遇到的程序开发问题。
如果觉得大佬教程网站内容还不错,欢迎将大佬教程推荐给程序员好友。
本图文内容来源于网友网络收集整理提供,作为学习参考使用,版权属于原作者。
如您有任何意见或建议可联系处理。小编QQ:384754419,请注明来意。