Post reply

Deleting 4000 different records from a table with where and conditions on each row.

  • June 15, 2011 at 10:34 am

    #239926

    Hello All

    I need suggestion to delete 4000 records from a table and every row has 'where' and 'AND' conditions.

    Records to be deleted I have in excel sheet.

    What is the best way to delete? Which query should I use to handle this task so I can delete in one go.

    Here I what I used for select command per record.

    Select * from PD7333.F980351 WHERE VRPDI ='R590012' AND VRVERS = 'DETEST01';

    Thanks

    Deena

  • June 15, 2011 at 10:46 am

    #1339124

    You should be able to replace your "select *" with a delete command. See below.

    begin tran

    delete from PD7333.F980351 WHERE VRPDI ='R590012' AND VRVERS = 'DETEST01';

    -- commit

    -- rollback

    After you have verified that the right amount of records were deleted, just select the commit and press F5. In case it did something you did not expect, select the rollback and press F5.

    "Any fool can write code that a computer can understand. Good programmers write
    code that humans can understand." -Martin Fowler et al, Refactoring: Improving the Design of Existing Code, 1999

  • June 15, 2011 at 10:47 am

    #1339125

    Each record to be deleted has entirely different criteria?

    Or is there something common amongst all of the records?

    Jason...AKA CirqueDeSQLeil
    _______________________________________________
    I have given a name to my pain...MCM SQL Server, MVP
    SQL RNNR
    Posting Performance Based Questions - Gail Shaw[/url]
    Learn Extended Events

  • June 15, 2011 at 11:14 am

    #1339153

    Thanks Jason

    there is something common amongst all of the records. I am using same where AND clause with two column name.

    just for example:

    select or Delete * from PD7333.F983051 WHERE VRPID = 'R00041' AND VRVERS = 'JD001'

    'R34234' AND VRVERS ='PD0001'

    'R34234' AND VRVERS = 'LOL001';

    THANKS

  • June 15, 2011 at 11:16 am

    #1339154

    I would suggest to use a SSIS package and lookup the excel. If the columns of the row gets satisfied in the excel lookup then use the OLEDB Command task and delete the row.

  • June 15, 2011 at 11:20 am

    #1339159

    You know how to use cursor? If only one run and nothing else, use a cursor. The arguments to be used for the deletion must be the result of a select. So you can use one at a time.

    If the arguments to be deleted are in txt, just use a text editor that automates the generation of the query.

    If the arguments are only in your head, enter all of them. LOL.

  • June 15, 2011 at 11:21 am

    #1339160

    deena 79174 (6/15/2011)

    Thanks Jason

    there is something common amongst all of the records. I am using same where AND clause with two column name.

    just for example:

    select or Delete * from PD7333.F983051 WHERE VRPID = 'R00041' AND VRVERS = 'JD001'

    'R34234' AND VRVERS ='PD0001'

    'R34234' AND VRVERS = 'LOL001';

    THANKS

    You have nothing else that is common between the records?

    Do you have this info built into a spreadsheet or something?

    Jason...AKA CirqueDeSQLeil
    _______________________________________________
    I have given a name to my pain...MCM SQL Server, MVP
    SQL RNNR
    Posting Performance Based Questions - Gail Shaw[/url]
    Learn Extended Events

  • June 15, 2011 at 11:24 am

    #1339164

    You can add an extra column in excel and build the dynamic sql string over there. Then just copy / paste.

    Obviously this is a fine solution only for 1 off work.

    Make sure you have a backup Before running this.

  • June 15, 2011 at 11:29 am

    #1339169

    Thanks all but I am very new to SQL...

  • June 15, 2011 at 11:34 am

    #1339173

    If there is nothing about those 4000 records to be deleted that makes them similar to one another, then you have to delete 1 by one. If you can find some detail from the records that makes them similar, you may be able to delete all 4000 records in one statement.

    Jason...AKA CirqueDeSQLeil
    _______________________________________________
    I have given a name to my pain...MCM SQL Server, MVP
    SQL RNNR
    Posting Performance Based Questions - Gail Shaw[/url]
    Learn Extended Events

  • June 15, 2011 at 12:33 pm

    #1339216

    SQLRNNR (6/15/2011)

    If there is nothing about those 4000 records to be deleted that makes them similar to one another, then you have to delete 1 by one. If you can find some detail from the records that makes them similar, you may be able to delete all 4000 records in one statement.

    an example of what Jason is refering to is like this: based on one example you showed us, this would delete more than one row:

    select *

    --or

    --Delete

    from PD7333.F983051

    WHERE VRPID = 'R00041' AND VRVERS IN('JD001','PD0001','LOL001')

    Lowell

    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • June 15, 2011 at 12:38 pm

    #1339222

    Here's what I meant (got the atachement from message conversation).

  • June 15, 2011 at 12:46 pm

    #1339227

    Thanks Ninja and everyone

  • June 15, 2011 at 12:51 pm

    #1339232

    You're welcome.

    Jason...AKA CirqueDeSQLeil
    _______________________________________________
    I have given a name to my pain...MCM SQL Server, MVP
    SQL RNNR
    Posting Performance Based Questions - Gail Shaw[/url]
    Learn Extended Events

  • June 15, 2011 at 12:55 pm

    #1339238

    SQLRNNR (6/15/2011)

    You're welcome.

    And the posts counter bumps continue!

    No wonder you're catching up :hehe:.

Viewing 15 posts - 1 through 15 (of 20 total)

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