CTE and delete - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

CTE and delete

Author

Topic

pelegk2
Aged Yak Warrior

723 Posts

Posted - 2010-07-06 : 10:09:38

when i am trying to make a delete on a CTE, which is built of an inner join query i get the error :

quote:
View or function 'CTE' is not updatable because the modification affects multiple base tables.

this is the query


WITH CTE (recipient,startdate, Status,DuplicateCount)
AS
(
SELECT  recipient,startdate,Status,
ROW_NUMBER() OVER(PARTITION BY recipient,startdate ORDER BY tblStatus.id desc) AS DuplicateCount
FROM [TargetTable] inner join tblStatus on
TargetTable.Status=tblStatus.name
where Mt=1
)
--select * from CTE
delete from CTE
WHERE DuplicateCount > 1

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2010-07-06 : 10:20:44

Yes. The cte is not aware from which table you want to delete...

N 56°04'39.26"
E 12°55'05.63"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2010-07-06 : 10:22:58

[code];WITH CTE (pkCol,DuplicateCount)
AS
(
SELECT pkCol,
ROW_NUMBER() OVER(PARTITION BY recipient, startdate ORDER BY tblStatus.id desc) AS DuplicateCount
FROM [TargetTable]
inner join tblStatus on TargetTable.Status = tblStatus.name
where Mt = 1
)
delete tt from [TargetTable] AS tt
inner join CTE as x on x.pkcol = tt.pkcol
WHERE x.DuplicateCount > 1[/code]

N 56°04'39.26"
E 12°55'05.63"

pelegk2
Aged Yak Warrior

723 Posts

Posted - 2010-07-06 : 10:53:26

Peso is pkCol should be my Primay Key Column?
if yes i dont have one.
the main coulm which i see as keys are "recipient, startdate".

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2010-07-06 : 11:03:51

"recipient, startdate" is your composite primary key?

N 56°04'39.26"
E 12°55'05.63"

pelegk2
Aged Yak Warrior

723 Posts

Posted - 2010-07-06 : 11:06:50

i dont have a real PK on the table, beacuse this is a temp table wto which i upload data from files.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2010-07-06 : 11:11:05

Add an IDENTITY column to your temp table? And make the identity column the PRIMARY KEY and CLUSTERED?

N 56°04'39.26"
E 12°55'05.63"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2010-07-06 : 11:12:12

;WITH CTE (recipient, startdate, DuplicateCount)
AS
(
SELECT recipient, startdate,
ROW_NUMBER() OVER(PARTITION BY recipient, startdate ORDER BY tblStatus.id desc) AS DuplicateCount
FROM [TargetTable]
inner join tblStatus on TargetTable.Status = tblStatus.name
where Mt = 1
)
delete tt from [TargetTable] AS tt
inner join CTE as x on x.recipient = tt.recipient and x.startdate = tt.startdate
WHERE x.DuplicateCount > 1

N 56°04'39.26"
E 12°55'05.63"

pelegk2
Aged Yak Warrior

723 Posts

Posted - 2010-07-06 : 16:16:56

10X but the idea of PK seems to be the correct answer i will check it out thanks

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources