Effective way to delete 2000 records out of 4000

  • Hi All

    I have need to know of an effective way to delete 2000 records from a table holding 4000 records. Their is a unique key ie. originalno. which identifies each record and also I have the originalno. of all the records that need to be deleted.

    But what syntax/way do I use to make this quick and correct rather than going through each originalno, and placing comers after each to extract the relevant records for deletion?

    Many Thanks in advance.......

  • If you don't care which 2000 get deleted you can use the following which will delete the first 2000 rows it finds:

    set rowcount 2000

    delete from tablename

    set rowcount 0

    More than likely you do want only specific ones deleted and that gets more difficult unless you can post a copy of the table schema and a couple of sample record (4 records, 2 that you wouldn't want deleted and 2 that you would like deleted)

    Baring that the following would work if the "originalno" values are sequential, or at least not interspersed with the values of the records to be kept:

    delete from tablename where originalno < 2000

    or

    delete from tablename where originalno between 1000 and 3000

    etc

    Without knowing what the table looks like and the type of data it would be difficult to help further.

    James.

  • You say that you have the OriginalNo values for the 2000 rows you want to delete.  Where do you have these values?  Please reply and include your table DDL.

    John Rowan

    ======================================================
    ======================================================
    Forum Etiquette: How to post data/code on a forum to get the best help[/url] - by Jeff Moden

  • I haven't tried this but I would assume you could use the folowing statement...

    delete top 2000 from *table


    Cheers,

    Ben Sullins
    bensullins.com
    Beer is my primary key...

  • The orginal post suggests that there are 2000 specific rows that need deleted. 

    John Rowan

    ======================================================
    ======================================================
    Forum Etiquette: How to post data/code on a forum to get the best help[/url] - by Jeff Moden

  • You say you have the 2000 to delete? Where are they stored now.

  • If you are getting the 2000 IDs that you want deleted from some sort of query, you would use:

    DELETE FROM Table_Name WHERE ID IN (*Query you used to pull the ID's you want deleted*)

    If you have them some other way, it would depend on the data/organization of the list of IDs.

  • Hi All

    thanks for the posts, but I have 2000 records from 4000 in data type varchar and in a non sequential format which need to be deleted. Iam new to sql and need to delete them in a quick and effective way rather than me going through and manually inputting each record id then a comma (,) and then selecting delete records.

    I look forward to your response.

    Many Thanks

  • I doubt anyone is going to be able to help if you don't post the table definition, some sample data and the criteria you would use to do the deletes currently.  Then we can see what you are trying to do and tell you the easiest way to accomplish it.  If you provide about 4 sample records two of which meet your delete criteria someone should be able to quickly help you.

    James.

  • And please provide the query that allowed you to select which rows you want deleted!  Stop making us guess!

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

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

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