delete top 100

  • 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

  • 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/

  • 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

  • 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
  • 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:

  • 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
  • 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
  • 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/

  • 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

  • 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/

  • GilaMonster (7/17/2013)


    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

    I know you can't do it directly but the point is valid. Issuing a delete top(x) with no way to order them makes no sense, unless of course the delete is intended to be inside a batching delete process where the order doesn't matter as long as they all get deleted.

    _______________________________________________________________

    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/

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

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