Hi Guys,
I need your help and assistance.
I am trying to update a table with duplicates in prod database. But, is giving me an error, Msg 4405, Level 16, State 1, Line 36
View or function 'CTE_Dups' is not updatable because the modification affects multiple base tables.
However, when I used temp table, it works but the based objects. The table to be updates is related or associated with about 6 tables, foreign keys. I have provided a simple data table I am trying update. Any help would be appreciated.
USE [PinPointPercs]
GO
IF OBJECT_ID('tempdb..#BaseTable') IS NOT NULL
DROP TABLE #BaseTable
Create table #BaseTable(
TitelNumber varchar(20) not null
,TitleDate datetime
,Projectid int
,TenureID int
)
INSERT into #BaseTable(titelNumber
,TitleDate
,Projectid
,TenureID
)
Values('SYK379116','2015-07-31 00:00:00.000',100,40
) ,
('SYK379116','2015-07-31 00:00:00.000',100,40
)
begin tran
;WITH CTE_Dups AS(
SELECT ROW_NUMBER() OVER
(PARTITION BY titelNumber,Projectid,TenureID,TitleDate
Order BY TitleDate DESC )
AS DupRowNumber
,T.*
FROM #BaseTable AS T
--WHERE EXISTS (SELECT *
-- FROM dbo.lookupTable AS R
-- WHERE T.titelNumber = R.titelNumber
-- AND T.ProjectID = R.ProjectID
-- AND T.TenureID = R.TenureID
-- AND T.EditionDate = R.EditionDate)
WHERE T.ProjectID = 100
)
DELETE FROM CTE_Dups Where DupRowNumber > 1
and projectid = 100
Thank you in advance
J
