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 12»»

delete top 100 Expand / Collapse
Author
Message
Posted Wednesday, July 17, 2013 12:56 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, November 4, 2013 8:13 AM
Points: 190, Visits: 367
hi
my query is giving me erro

delete top(1000) from table a
join table b
on a.id = b.id
and b.date < getdate()

error: incorrect syntax near a.

i want to delete top 1000 from table a
Post #1474771
Posted Wednesday, July 17, 2013 1:27 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 2:34 PM
Points: 13,481, Visits: 12,342
riya_dave (7/17/2013)
hi
my query is giving me erro

delete top(1000) from table a
join table b
on a.id = b.id
and b.date < getdate()

error: incorrect syntax near a.

i want to delete top 1000 from table a


You have a couple of major issues here. For one, you don't specify what you are deleting. What table's rows are you wanting to delete? The next issue is top(1000). You have no order by so there is no way to know which 1000 it will delete.

If you could post some details we can help.


_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Post #1474783
Posted Wednesday, July 17, 2013 3:13 PM


Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Yesterday @ 8:06 PM
Points: 581, Visits: 2,711
As Sean said, you need an ORDER BY statement.

I created some sample data and a query that you could use for reference. I am a fan of using the CTE approach for this kind of thing. You need something more like this:

-- (1) Some sample data
DECLARE @sampleData1 TABLE (id int primary key, data varchar(36) not null);
DECLARE @sampleData2 TABLE (id int unique not null);

WITH sampleData AS
( SELECT ROW_NUMBER() OVER (ORDER BY (SELECT 0)) AS id,
NEWID() AS data
FROM sys.all_columns)
INSERT INTO @sampleData1
SELECT * FROM sampleData
WHERE id<=20;

INSERT INTO @sampleData2
VALUES (1),(2),(3),(4),(5),(6),(7),(8),(9),(10);

-- review the output pre-delete
SELECT * FROM @sampleData1;

WITH DeleteThisStuff AS
(
SELECT TOP 10 s1.id
FROM @sampleData1 s1
JOIN @sampleData2 s2 ON s1.id=s2.id
ORDER BY s1.id
)
DELETE FROM @sampleData1
WHERE id IN (SELECT id FROM DeleteThisStuff)

SELECT * FROM @sampleData1;

Note: I did TOP 10 for an easier-to-read result set. Let us know if this helps.


-- Alan Burstein



Read this article for best practices on asking questions.
Need to split a string? Try this (Jeff Moden)
Need a pattern-based string spitter? Try this (Dwain Camps)
My blog
Post #1474809
Posted Wednesday, July 17, 2013 3:24 PM


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: Today @ 3:18 PM
Points: 3,667, Visits: 8,002
There's no need for an ORDER BY if the ultimate goal is to delete all the rows that follow that condition. However, a cycle will be needed. It's common to use this to avoid commiting very large transactions.

As Sean said, you need to specify what table are you going to delete rows from. Adding the table alias between "top(1000)" and "from" would do the trick.




Luis C.
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

Forum Etiquette: How to post data/code on a forum to get the best help
Post #1474814
Posted Wednesday, July 17, 2013 3:29 PM


SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Tuesday, June 17, 2014 10:17 AM
Points: 31, Visits: 278
riya_dave (7/17/2013)
hi
my query is giving me erro

delete top(1000) from table a
join table b
on a.id = b.id
and b.date < getdate()

error: incorrect syntax near a.

i want to delete top 1000 from table a



Post #1474817
Posted Wednesday, July 17, 2013 3:38 PM


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: Today @ 3:18 PM
Points: 3,667, Visits: 8,002
Voide (7/17/2013)
riya_dave (7/17/2013)
hi
my query is giving me erro

delete top(1000) from table a
join table b
on a.id = b.id
and b.date < getdate()

error: incorrect syntax near a.

i want to delete top 1000 from table a




What surprises you? It's totally valid and equal to a where in this case.



Luis C.
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

Forum Etiquette: How to post data/code on a forum to get the best help
Post #1474822
Posted Wednesday, July 17, 2013 3:58 PM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 3:43 PM
Points: 43,047, Visits: 36,206
Sean Lange (7/17/2013)
The next issue is top(1000). You have no order by so there is no way to know which 1000 it will delete.


You can't put an Order By on a delete.

DELETE TOP (100) FROM SomeTable
ORDER BY ID

Msg 156, Level 15, State 1, Line 4
Incorrect syntax near the keyword 'ORDER'.


You can put an order by into a CTE (the select) and delete from the CTE, you can put a select with order by and use IN, but you can't put an order by onto a delete directly



Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #1474826
Posted Thursday, July 18, 2013 12:47 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Tuesday, September 9, 2014 3:27 AM
Points: 1,893, Visits: 2,329
If you specify alias with TOP in delete it will always gives you an error:

e.g.
DELETE TOP (2)
FROM tbl1 t



_______________________________________________________________
To get quick answer follow this link:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
Post #1474909
Posted Thursday, July 18, 2013 1:08 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Friday, September 12, 2014 9:43 AM
Points: 7,284, Visits: 13,824
kapil_kk (7/18/2013)
If you specify alias with TOP in delete it will always gives you an error:

e.g.
DELETE TOP (2)
FROM tbl1 t


Your syntax is incorrect:
DELETE TOP (2) t
FROM tbl1 t



delete top(1000) a
from table a
join table b
on a.id = b.id
and b.date < getdate()



“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
Exploring Recursive CTEs by Example Dwain Camps
Post #1474914
Posted Thursday, July 18, 2013 1:11 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Tuesday, September 9, 2014 3:27 AM
Points: 1,893, Visits: 2,329
ChrisM@Work (7/18/2013)
kapil_kk (7/18/2013)
If you specify alias with TOP in delete it will always gives you an error:

e.g.
DELETE TOP (2)
FROM tbl1 t


Your syntax is incorrect:
DELETE TOP (2) t
FROM tbl1 t



delete top(1000) a
from table a
join table b
on a.id = b.id
and b.date < getdate()


OK
Thanks Chris for correcting me



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

Add to briefcase 12»»

Permissions Expand / Collapse