Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Question of the Day for 08 Nov 2005 Expand / Collapse
Author
Message
Posted Thursday, October 27, 2005 6:05 PM
SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Friday, May 18, 2007 3:36 PM
Points: 10,039, Visits: 1
Comments posted to this topic are about the Question of the Day for 08 Nov 2005 posted at http://www.sqlservercentral.com/testcenter/qod.asp?QuestionID=639.
Post #233177
Posted Tuesday, November 8, 2005 10:26 AM
Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Tuesday, July 8, 2014 12:24 PM
Points: 3,475, Visits: 579

I am always cautious to delete anything from the top. You delete the first line, the second line becomes the first line etc.

Addionally, why did the question say "remove from a data load" ? When you are removing from a data load, it is being removed from the source file during the process of loading. In this case it is just deleting from a regular table.

 




Regards,
Yelena Varshal

Post #235774
Posted Tuesday, November 8, 2005 10:55 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Saturday, February 28, 2009 4:25 PM
Points: 20, Visits: 5

Only in SS2K5 can you use the TOP keyword with DELETE.  Wouldn't this work for any version (and still use TOP)?:

DELETE customers FROM (SELECT TOP 10 * FROM customers ORDER BY DateAdd DESC)



Post #235794
Posted Tuesday, November 8, 2005 11:37 AM


SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Thursday, November 13, 2008 9:13 AM
Points: 499, Visits: 76
I get a syntax error in 2005 with any of these 4 commands. The "correct" one gives this error:

Incorrect syntax near the keyword 'order'.

I don't think you can have an order by clause with a delete statement, even in 2005. Am I missing something?

Post #235810
Posted Wednesday, November 9, 2005 2:02 PM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Friday, July 18, 2014 7:02 AM
Points: 1,140, Visits: 701

I believe you are correct.

The only way to do this is:

DELETE Customer
WHERE CustomerId IN
(
SELECT TOP 10 CustomerId
FROM Customer
ORDER BY DateAdded DESC
)

I'm not sure what DELETE FROM (SELECT TOP 10) does, but I wouldn't trust it without correlation, personally.


--
Adam Machanic
SQL Server MVP
SQLblog.com: THE SQL Server Blog Spot on the Web
Post #236155
Posted Wednesday, November 9, 2005 2:04 PM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Friday, July 18, 2014 7:02 AM
Points: 1,140, Visits: 701
Ahhh, answered my own question:

--
DECLARE @tbl table (id int)
insert @tbl values (1)
insert @tbl values (2)
insert @tbl values (3)

delete x from (select top 1 id from @tbl order by id desc) x

select * from @tbl
--

Result:

Server: Msg 4428, Level 16, State 1, Line 6
The derived table 'x' is not updatable because the definition contains the TOP clause.


--
Adam Machanic
SQL Server MVP
SQLblog.com: THE SQL Server Blog Spot on the Web
Post #236157
Posted Thursday, November 10, 2005 4:43 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Thursday, July 10, 2014 7:24 AM
Points: 1,060, Visits: 874
so, another top notch question then?


Post #236247
Posted Thursday, August 3, 2006 4:34 PM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Sunday, February 18, 2007 9:53 AM
Points: 289, Visits: 1
Hi,

can you tell me why this is incorrect ?

set rowcount 10 delete customers order by DateAdded desc set rowcount 0
Post #299508
Posted Monday, November 19, 2012 6:03 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Friday, July 25, 2014 12:34 AM
Points: 1,888, Visits: 2,326
nice and easy question to grab +2 points


_______________________________________________________________
To get quick answer follow this link:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
Post #1386334
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse