Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


delete top 100


delete top 100

Author
Message
riya_dave
riya_dave
SSC Veteran
SSC Veteran (204 reputation)SSC Veteran (204 reputation)SSC Veteran (204 reputation)SSC Veteran (204 reputation)SSC Veteran (204 reputation)SSC Veteran (204 reputation)SSC Veteran (204 reputation)SSC Veteran (204 reputation)

Group: General Forum Members
Points: 204 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
SSCoach
SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)

Group: General Forum Members
Points: 16542 Visits: 16997
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)
Alan.B
Alan.B
SSCrazy
SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)

Group: General Forum Members
Points: 2236 Visits: 7423
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
SSCrazy Eights
SSCrazy Eights (8.5K reputation)SSCrazy Eights (8.5K reputation)SSCrazy Eights (8.5K reputation)SSCrazy Eights (8.5K reputation)SSCrazy Eights (8.5K reputation)SSCrazy Eights (8.5K reputation)SSCrazy Eights (8.5K reputation)SSCrazy Eights (8.5K reputation)

Group: General Forum Members
Points: 8488 Visits: 18099
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 Rookie
SSC Rookie (30 reputation)SSC Rookie (30 reputation)SSC Rookie (30 reputation)SSC Rookie (30 reputation)SSC Rookie (30 reputation)SSC Rookie (30 reputation)SSC Rookie (30 reputation)SSC Rookie (30 reputation)

Group: General Forum Members
Points: 30 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
SSCrazy Eights
SSCrazy Eights (8.5K reputation)SSCrazy Eights (8.5K reputation)SSCrazy Eights (8.5K reputation)SSCrazy Eights (8.5K reputation)SSCrazy Eights (8.5K reputation)SSCrazy Eights (8.5K reputation)SSCrazy Eights (8.5K reputation)SSCrazy Eights (8.5K reputation)

Group: General Forum Members
Points: 8488 Visits: 18099
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-Forever
SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)

Group: General Forum Members
Points: 47189 Visits: 44356
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
SSCrazy
SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)

Group: General Forum Members
Points: 2417 Visits: 2763
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
SSCrazy Eights
SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)

Group: General Forum Members
Points: 8957 Visits: 19014
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
SSCrazy
SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)

Group: General Forum Members
Points: 2417 Visits: 2763
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