delete top 100

Question

delete top 100

riya_dave

Hall of Fame

Points: 3106
More actions
July 17, 2013 at 12:56 pm

#280187

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

Viewing 11 posts - 1 through 10 (of 10 total)

You must be logged in to reply to this topic. Login to reply

Sean Lange SSC Guru Points: 286590 More actions · Answer 1

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 http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

Alan Burstein SSC Guru Points: 61141 More actions · Answer 2

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

(SELECTROW_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.

"I cant 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."

-- Itzik Ben-Gan 2001

Luis Cazares SSC Guru Points: 183706 More actions · Answer 3

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 Mr or Mrs. 500 Points: 548 More actions · Answer 4

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 SSC Guru Points: 183706 More actions · Answer 5

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

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 6

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 SSC-Insane Points: 21316 More actions · Answer 7

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 SSC Guru Points: 186127 More actions · Answer 8

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

kapil_kk SSC-Insane Points: 21316 More actions · Answer 9

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