none
Delete a variable number of records

    Question

  • Can I do something like this?

    DECLARE @del AS INT
    SET @del = 10

    DELETE TOP(@del) FROM TABLE

    Tuesday, January 07, 2014 7:11 PM

Answers

  • Tom,

    Look closer in the examples provided in your link

    B. Using TOP with a variable

    The following example uses a variable to specify the number of employees that are returned in the query result set.

    USE AdventureWorks2012;
    GO
    DECLARE @p AS int = 10;
    SELECT TOP(@p)JobTitle, HireDate, VacationHours
    FROM HumanResources.Employee
    ORDER BY VacationHours DESC
    GO

    You can use variable just fine starting from the SQL 2005 version.


    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog


    My TechNet articles

    • Marked as answer by shiftbit Wednesday, January 08, 2014 12:56 AM
    Tuesday, January 07, 2014 7:34 PM

All replies

  • You can do what you want:

    DECLARE @del AS INT
     SET @del = 10
    delete top(@del) from images

    Or try this sample:

    DECLARE @del AS INT
     SET @del = 10
    ;with mycte as (
    select id,groupId,rowposition, ROW_NUMBER() Over(partition by groupid order by groupId ) rn from images)
    
    
    delete from mycte WHERE rn<=@del 


    Tuesday, January 07, 2014 7:25 PM
  • Tom,

    Look closer in the examples provided in your link

    B. Using TOP with a variable

    The following example uses a variable to specify the number of employees that are returned in the query result set.

    USE AdventureWorks2012;
    GO
    DECLARE @p AS int = 10;
    SELECT TOP(@p)JobTitle, HireDate, VacationHours
    FROM HumanResources.Employee
    ORDER BY VacationHours DESC
    GO

    You can use variable just fine starting from the SQL 2005 version.


    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog


    My TechNet articles

    • Marked as answer by shiftbit Wednesday, January 08, 2014 12:56 AM
    Tuesday, January 07, 2014 7:34 PM
  • Yes.
    Tuesday, January 07, 2014 7:42 PM
  • please read the below :

    http://technet.microsoft.com/en-us/library/ms189463.aspx

    to be confident


    Ahsan Kabir Please remember to click Mark as Answer and Vote as Helpful on posts that help you. This can be beneficial to other community members reading the thread. http://www.aktechforum.blogspot.com/

    Tuesday, January 07, 2014 9:13 PM
  • Yes you can.  Sorry I was looking at something else.  I had never tried a variable.
    Tuesday, January 07, 2014 9:13 PM
  • What are you trying to do?  A table has no ordering, so the non-relational, propriety TOP() is random without  an ORDER BY clause to turn it into a sequential file structure.  SQL is set-oriented, not a mag tape file model. What we want is a search condition in a WHERE clause. 

    --CELKO-- Books in Celko Series for Morgan-Kaufmann Publishing: Analytics and OLAP in SQL / Data and Databases: Concepts in Practice Data / Measurements and Standards in SQL SQL for Smarties / SQL Programming Style / SQL Puzzles and Answers / Thinking in Sets / Trees and Hierarchies in SQL

    Tuesday, January 07, 2014 10:25 PM