SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


delete top 100


delete top 100

Author
Message
riya_dave
riya_dave
Mr or Mrs. 500
Mr or Mrs. 500 (530 reputation)Mr or Mrs. 500 (530 reputation)Mr or Mrs. 500 (530 reputation)Mr or Mrs. 500 (530 reputation)Mr or Mrs. 500 (530 reputation)Mr or Mrs. 500 (530 reputation)Mr or Mrs. 500 (530 reputation)Mr or Mrs. 500 (530 reputation)

Group: General Forum Members
Points: 530 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
Sean Lange
Sean Lange
SSC Guru
SSC Guru (65K reputation)SSC Guru (65K reputation)SSC Guru (65K reputation)SSC Guru (65K reputation)SSC Guru (65K reputation)SSC Guru (65K reputation)SSC Guru (65K reputation)SSC Guru (65K reputation)

Group: General Forum Members
Points: 65010 Visits: 17979
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 Modens 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)
Alan.B
Alan.B
SSChampion
SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)

Group: General Forum Members
Points: 13892 Visits: 8015
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



Best practices for getting help on SQLServerCentral
Need to split a string? Try DelimitedSplit8K or DelimitedSplit8K_LEAD (SQL 2012+)
Need a pattern-based splitter? Try PatternSplitCM
Need to remove or replace those unwanted characters? Try PatExclude8K and PatReplace8K.

"I can't stress enough the importance of switching from a 'sequential files' mindset to 'set-based' thinking. After you make the switch, you can spend your time tuning and optimizing your queries instead of maintaining lengthy, poor-performing code. " -- Itzek Ben-Gan 2001
Luis Cazares
Luis Cazares
SSC-Forever
SSC-Forever (43K reputation)SSC-Forever (43K reputation)SSC-Forever (43K reputation)SSC-Forever (43K reputation)SSC-Forever (43K reputation)SSC-Forever (43K reputation)SSC-Forever (43K reputation)SSC-Forever (43K reputation)

Group: General Forum Members
Points: 43322 Visits: 19859
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.
General Disclaimer:
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?


How to post data/code on a forum to get the best help: Option 1 / Option 2
Voide
Voide
SSC-Enthusiastic
SSC-Enthusiastic (104 reputation)SSC-Enthusiastic (104 reputation)SSC-Enthusiastic (104 reputation)SSC-Enthusiastic (104 reputation)SSC-Enthusiastic (104 reputation)SSC-Enthusiastic (104 reputation)SSC-Enthusiastic (104 reputation)SSC-Enthusiastic (104 reputation)

Group: General Forum Members
Points: 104 Visits: 280
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



Blink
Luis Cazares
Luis Cazares
SSC-Forever
SSC-Forever (43K reputation)SSC-Forever (43K reputation)SSC-Forever (43K reputation)SSC-Forever (43K reputation)SSC-Forever (43K reputation)SSC-Forever (43K reputation)SSC-Forever (43K reputation)SSC-Forever (43K reputation)

Group: General Forum Members
Points: 43322 Visits: 19859
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



Blink

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


Luis C.
General Disclaimer:
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?


How to post data/code on a forum to get the best help: Option 1 / Option 2
GilaMonster
GilaMonster
SSC Guru
SSC Guru (233K reputation)SSC Guru (233K reputation)SSC Guru (233K reputation)SSC Guru (233K reputation)SSC Guru (233K reputation)SSC Guru (233K reputation)SSC Guru (233K reputation)SSC Guru (233K reputation)

Group: General Forum Members
Points: 233499 Visits: 46361
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, MVP, M.Sc (Comp Sci)
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


kapil_kk
kapil_kk
SSCertifiable
SSCertifiable (5.5K reputation)SSCertifiable (5.5K reputation)SSCertifiable (5.5K reputation)SSCertifiable (5.5K reputation)SSCertifiable (5.5K reputation)SSCertifiable (5.5K reputation)SSCertifiable (5.5K reputation)SSCertifiable (5.5K reputation)

Group: General Forum Members
Points: 5476 Visits: 2768
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/
ChrisM@Work
ChrisM@Work
SSC-Forever
SSC-Forever (42K reputation)SSC-Forever (42K reputation)SSC-Forever (42K reputation)SSC-Forever (42K reputation)SSC-Forever (42K reputation)SSC-Forever (42K reputation)SSC-Forever (42K reputation)SSC-Forever (42K reputation)

Group: General Forum Members
Points: 42973 Visits: 20015
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
kapil_kk
kapil_kk
SSCertifiable
SSCertifiable (5.5K reputation)SSCertifiable (5.5K reputation)SSCertifiable (5.5K reputation)SSCertifiable (5.5K reputation)SSCertifiable (5.5K reputation)SSCertifiable (5.5K reputation)SSCertifiable (5.5K reputation)SSCertifiable (5.5K reputation)

Group: General Forum Members
Points: 5476 Visits: 2768
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/
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search