Sign in
 
Home20122008 R2200820052000LibraryForums
SQL Server >
> 
> 
sql delete

Answered sql delete

  • Thursday, March 21, 2013 10:36 PM
     
     
    Sign In to Vote
    0

    I have a question about how to delete something from one of my tables.  I'm afraid to try it because I don't want to accidentally delete the wrong thing!

     

    What I have is basically this:

     

    Products table that contains ProductID, etc.

    ProductOptionsLink that contains ProductID, DetailID, and some other stuff. 

     

    What I want to do get a specific DetailID from a dropdown box, and delete from the Products table all products that have this value as their DetailID in the ProductOptionsLink table.  So if product number 1000 has a DetailID of 100 and that is the value selected in the dropdown box, it will delete product 1000 from the products table. 

     

All Replies

  • Thursday, March 21, 2013 10:43 PM
     
     Answered
    Sign In to Vote
    0

    Just try the delete statement in Transaction

    First do a select on the tables

    SELECT *

    FROM products A

    JOIN ProductOptionsLink B

    ON A.ProductID = B.ProductID

    WHERE B.DetailID= pass the value

    See are those the required records. Then start a transaction

    BEGIN TRAN

    DELETE A

    FROM products A

    JOIN ProductOptionsLink B

    ON A.ProductID = B.ProductID

    WHERE B.DetailID= pass the value

    SELECT * FROM Products

    Look if every thing is good If so

    COMMIT TRAN

    If thats not the expected...

    ROLLBACK TRAN


     
  • Thursday, March 21, 2013 10:56 PM
     
     
    Sign In to Vote
    0

    DELETE Products
    FROM   Products P
    WHERE  EXISTS (SELECT *
                   FROM   ProductOptionsLink POL
                   WHERE  P.ProductID = POL.ProductID
                     AND  POL.DetailID = 100)

    To verify that this does the right thing you can do:

    BEGIN TRANSACTION

    DELETE Products
    FROM   Products P
    WHERE  EXISTS (SELECT *
                   FROM   ProductOptionsLink POL
                   WHERE  P.ProductID = POL.ProductID
                     AND  POL.DetailID = 100)

    SELECT * FROM Products ORDER BY DetailID
    go
    ROLLBACK TRANSACTION

    If you were in error, everything is rolled back to the original state before BEGIN TRANSACTION.

    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
     
  • Thursday, March 21, 2013 11:43 PM
     
     
    Sign In to Vote
    0

    Thanks! That worked perfectly when I tried it in SSMS.  One more question.  Can I use this code in the .aspx.cs file and save it as the SQL Command?

    I am pretty new to this.

     
  • Thursday, March 21, 2013 11:45 PM
     
     
    Sign In to Vote
    0
    Thanks! I used the code posted by Harsha Lella above and it worked but I see yours has WHERE EXISTS... is there an advantage to that? 
     
  • Friday, March 22, 2013 12:08 AM
     
     
    Sign In to Vote
    0
    Where exists internally does a semi join. If there is one occurance, it will pick up and doesn't check each and every occurance.
     
  • Friday, March 22, 2013 8:25 AM
     
     
    Sign In to Vote
    0

    Thanks! I used the code posted by Harsha Lella above and it worked but I see yours has WHERE EXISTS... is there an advantage to that? 

    It depends on whom you ask.

    The problem with FROM-JOIN is that if one row in the target can match multiple rows in the source, the results can be non-deterministic. This can be particularly problemetic with UPDATE. For this reason, people of a purist nature frown on the syntax Harasha used.

    Many people feel more comfortable with the FROM-JOIN syntax, and correctly used there is no problem with it.

    Then again, it's not a bad thing to learn some more SQL tricks to have in your bag, isn't it?

    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se