May 6, 2012 at 8:47 am
CREATE TABLE Test
(
ContactID INT,
SalesOrderID INT,
OrderYear DATETIME
)
insert into Test(ContactID,SalesOrderID,OrderYear)
select top 10 ContactID,SalesOrderID,YEAR(OrderDate) as orderyear from AdventureWorks.Sales.SalesOrderHeader
order by ContactID asc
ContactID SalesOrderID,OrderYear
1441322001
1455792002
1463892002
1474542002
1483952002
1494952003
1507562003
2534592003
2589072003
2651572004
Delete fails when i try to delete using Common Table Expressions
here is query
WITH C AS(
select *, ROW_NUMBER() OVER(PARTITION BY ContactID ORDER BY(SELECT 0)) AS EN from Test
)
DELETE FROM C WHERE EN>1
But the same works fine when you use derived table query
here is query
delete from a from (
select ContactID,SalesOrderID,OrderYear
,ROW_NUMBER() over(partition by contactid order by (Select 0)) as a
from Test) as a
where a>1
What could be the problem
May 6, 2012 at 8:59 am
Is
delete from a from ... valid?
If it is, in my defence I currently only query data (I'm currently only a consumer of data, not a maintainer)
It is valid. I should have tested it first - to me, it just looked wrong.:blush:
Dave
May 6, 2012 at 1:30 pm
Smash125 (5/6/2012)
CREATE TABLE Test(
ContactID INT,
SalesOrderID INT,
OrderYear DATETIME
)
insert into Test(ContactID,SalesOrderID,OrderYear)
select top 10 ContactID,SalesOrderID,YEAR(OrderDate) as orderyear from AdventureWorks.Sales.SalesOrderHeader
order by ContactID asc
ContactID SalesOrderID,OrderYear
1441322001
1455792002
1463892002
1474542002
1483952002
1494952003
1507562003
2534592003
2589072003
2651572004
Delete fails when i try to delete using Common Table Expressions
here is query
WITH C AS(
select *, ROW_NUMBER() OVER(PARTITION BY ContactID ORDER BY(SELECT 0)) AS EN from Test
)
DELETE FROM C WHERE EN>1
But the same works fine when you use derived table query
here is query
delete from a from (
select ContactID,SalesOrderID,OrderYear
,ROW_NUMBER() over(partition by contactid order by (Select 0)) as a
from Test) as a
where a>1
What could be the problem
You say the delete fails but you don't tell us if it simply fails to delete any records or if you get an error message. If you get an error message, please share the complete and unabridged text of the error.
May 6, 2012 at 11:09 pm
I do not get any error message.When i execute the query i get the message
(0 row(s) affected)
May 7, 2012 at 12:07 am
Okay, can't answer your question as both queries work for me with no issues.
May 7, 2012 at 1:23 am
OK Will Check again 🙂
May 7, 2012 at 5:42 am
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply