November 29, 2009 at 12:46 pm
Hello experts,
I’m having a challenge in writing a simple delete statement. I’ve a table (FirstLevelChangeDetail) which has same number to 2 fields (CapturedDate & DbTotal) however there is the 3rd entity which is RecordNumber and it’s different. I’ve 5 rows which have same CapturedDate and DbTotal but Different RecordNumber. I still need to delete them even though they are different records. When I wrote the following select statement it pulled exactly the same rows which I need to delete.
-------------------------------------
Declare @n int
select @n = count (RecordNumber)
from FirstLevelChangeDetail
where CapturedDate = (select distinct top 1 CapturedDate from FirstLevelChangeDetail order by CapturedDate desc)
print @n
set @n = @N-1
print @n
select top (@n) *
from FirstLevelChangeDetail
order by RecordNumber desc
-------------------------------------
However when I replace select top (@n) with delete I end up having an error saying
‘Msg 156, Level 15, State 1, Line 11
Incorrect syntax near the keyword 'order'.’
Here is my delete code.
-------------------------------------
Use CIAdeel
Go
Declare @n int
select @n = count (RecordNumber)
from FirstLevelChangeDetail
where CapturedDate = (select distinct top 1 CapturedDate from FirstLevelChangeDetail order by CapturedDate desc)
print @n
set @n = @N-1
print @n
delete top (@n)
from FirstLevelChangeDetail
order by RecordNumber desc
-------------------------------------
Does anybody see what’s wrong and how I can fix it?
Thanks a lot in advance.
November 29, 2009 at 1:23 pm
You cannot use an ORDER BY clause in a DELETE statement. Wouldn't make any sense anyway.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
November 29, 2009 at 2:15 pm
mr_adeelalisyed
You may want to use code like this:
CREATE TABLE #FirstLevelChangeDetail(CapturedDate DATETIME,DbTotal INT,RecordNumber INT)
INSERT INTO #FirstLevelChangeDetail
SELECT '1/1/09',20,1 UNION ALL
SELECT '1/1/09',50,3 UNION ALL
SELECT '1/1/09',21,5 UNION ALL
SELECT '1/2/09',50,1 UNION ALL
SELECT '1/3/09',50,1
;with numbered as(SELECT rowno=row_number() over
(partition by CapturedDate order by RecordNumber),CapturedDate, DbTotal,RecordNumber
from #FirstLevelChangeDetail)
--For testing use:
SELECT * FROM numbered --To check if output is correct
--Once tested and output is correct replace above statement with
--DELETE from numbered WHERE rowno > 1
SELECT results:
rownoCaptured Date DbTotal RecordNumber
12009-01-01 00:00:00.000 201
22009-01-01 00:00:00.000 503
32009-01-01 00:00:00.000 215
12009-01-02 00:00:00.000 501
12009-01-03 00:00:00.000 501
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply