Results 1 to 14 of 14
  1. #1
    Join Date
    Jul 2003
    Location
    Michigan
    Posts
    1,941

    Unanswered: A case of "OverDeletion"

    I added this line to a sproc yesterday:

    --get rid of the product master for inactive raw-mats/packaging
    DELETE FROM PRODUCT_MASTER
    WHERE PRODUCT IN
    (SELECT PRODUCT FROM SAPROD_PLAN.DBO.PARTMSTR
    WHERE PART_STATUS IN ('I','D') AND PART_TYPE NOT IN ('F','N','K','D'))


    This sql statement should have thrown an error, because there is no column named "product" in the table partmstr. Instead, all of the data in the table
    product_master was deleted. Of course, this table cascade deletes to 18 other tables, so I lost a whole slew of data. No big deal there, I can get a tape backup. Just curious how it could have even happened in the first place.
    Inspiration Through Fermentation

  2. #2
    Join Date
    Jun 2003
    Posts
    269

    Smile

    Quote Originally Posted by RedNeckGeek
    I added this line to a sproc yesterday:

    --get rid of the product master for inactive raw-mats/packaging
    DELETE FROM PRODUCT_MASTER
    WHERE PRODUCT IN
    (SELECT PRODUCT FROM SAPROD_PLAN.DBO.PARTMSTR
    WHERE PART_STATUS IN ('I','D') AND PART_TYPE NOT IN ('F','N','K','D'))


    This sql statement should have thrown an error, because there is no column named "product" in the table partmstr. Instead, all of the data in the table
    product_master was deleted. Of course, this table cascade deletes to 18 other tables, so I lost a whole slew of data. No big deal there, I can get a tape backup. Just curious how it could have even happened in the first place.
    u are kidding.Delete statement will raise error if any column name u mentioned in sql statement which is not in ur table.
    Im 100% sure deletion will not happened in such case.
    I love cursor,though I never use it.Cos' I dont want to misuse it.
    ----------------------------------------------

    ----cheers
    ----http://mallier.blogspot.com

  3. #3
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    U funny

    You should post the DDL for both tables and provide some sample data.

    Look at the sticky's at the top of the page, but....

    Code:
    USE Northwind
    GO
    
    SET NOCOUNT ON
    SELECT * INTO myOrders99 FROM Orders
    GO
    
    sp_help myOrders99
    GO
    
    DELETE FROM myOrders99 WHERE Brett IN (SELECT Brett FROM myOrders99)
    GO
    
    SET NOCOUNT OFF
    DROP TABLE myOrders99
    GO
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  4. #4
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    Do you have a couple of PARTMSTR tables? I see this one is declared with the database name, as well as owner.

  5. #5
    Join Date
    Jul 2003
    Location
    Michigan
    Posts
    1,941
    This duplicates the problem...
    Code:
    CREATE PROCEDURE TMPTEST
    AS
    CREATE TABLE TMPPARTMSTR (
    PART_CODE 	VARCHAR(18),
    PART_TYPE	CHAR(1),
    PART_STATUS	CHAR(1),
    )
    
    CREATE TABLE TMPPRODUCT_MASTER(
    PRODUCT VARCHAR(18)
    )
    
    INSERT INTO TMPPARTMSTR (PART_CODE, PART_TYPE, PART_STATUS)
    SELECT 'ABC123', 'F', 'A' UNION ALL
    SELECT 'DEF123','K','A' UNION ALL
    SELECT 'ABC456','R','A' UNION ALL
    SELECT 'DEF456','R','I'
    
    INSERT INTO TMPPRODUCT_MASTER(PRODUCT)
    SELECT 'ABC123' UNION ALL
    SELECT 'DEF123' UNION ALL
    SELECT 'ABC456' UNION ALL
    SELECT 'DEF456'
    
    DELETE FROM TMPPRODUCT_MASTER
    WHERE     (PRODUCT IN
                              (SELECT     PRODUCT
                                FROM          TMPPARTMSTR
                                WHERE      PART_STATUS IN ('I', 'D') AND PART_TYPE NOT IN ('F', 'N', 'K', 'D')))
    GO
    It ends up deleting all 4 rows from tmpproduct_master, when it should throw an error.
    Inspiration Through Fermentation

  6. #6
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    It looks like it's coorelating the column without a lable because it's unique

    Code:
    SELECT * FROM TMPPRODUCT_MASTER
          WHERE PRODUCT IN ( SELECT PRODUCT
    			   FROM TMPPARTMSTR
    			  WHERE PART_STATUS   IN ('I', 'D') 
    			    AND PART_TYPE NOT IN ('F', 'N', 'K', 'D'))
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  7. #7
    Join Date
    Mar 2003
    Location
    Reading, UK
    Posts
    70
    Hi,
    Initially i found this query strange and thought that ur statement is not valid,but after testing it manually i found that the statement given is absolutely correct,and it even worked in the same way in oracle also..

    The only way one can restrict this is by adding the table name in front of the column name
    eg:
    delete from deal where dealid in (select tst1.dealid from tst1 where id1=1)
    where dealid is a column of deal table and id1 is the col of tst1 table..

    After checking the execution plan I can see that
    1.DB checks for the availability of clustered index for deal.dealid with the condition deal.dealid=deal.dealid(ordered)
    2.Scans the inner query for tst1.id1=1.

    one more weird thing which i have observed is
    delete from deal where dealid in (select tst1.dealid from tst1 where dealid=1)
    also works fine and deletes the rows from the deal table..

    May be this is a bug in SQL.

    regards,
    Pavan.

  8. #8
    Join Date
    Jun 2003
    Posts
    269
    Quote Originally Posted by RedNeckGeek
    This duplicates the problem...
    Code:
    DELETE FROM TMPPRODUCT_MASTER
    WHERE     (PRODUCT IN
                              (SELECT     PRODUCT
                                FROM          TMPPARTMSTR
                                WHERE      PART_STATUS IN ('I', 'D') AND PART_TYPE NOT IN ('F', 'N', 'K', 'D')))
    GO
    It ends up deleting all 4 rows from tmpproduct_master, when it should throw an error.
    PRODUCT column is not in table TMPPARTMSTR and it is refered from TMPPRODUCT_MASTER .Obviously condition would be true and deleted all records.

    In future always add alias name with table and refer ur column with alias name.
    I love cursor,though I never use it.Cos' I dont want to misuse it.
    ----------------------------------------------

    ----cheers
    ----http://mallier.blogspot.com

  9. #9
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    It's not a bug.

    But, why does this work

    Code:
    DELETE FROM TMPPRODUCT_MASTER
          WHERE PRODUCT IN ( SELECT PART_CODE
    		           FROM TMPPARTMSTR
    		          WHERE PART_STATUS   IN ('I', 'D') 
    			    AND PART_TYPE NOT IN ('F', 'N', 'K', 'D'))
    
    
    SELECT * FROM TMPPRODUCT_MASTER
    But this does not? (Not firing all the neurons today I guess)

    Code:
    DELETE FROM TMPPRODUCT_MASTER o
          WHERE EXISTS ( SELECT *
    		       FROM TMPPARTMSTR i
    		      WHERE PART_STATUS   IN ('I', 'D') 
    			AND PART_TYPE NOT IN ('F', 'N', 'K', 'D')
    			AND i.PART_CODE = o.PRODUCT)
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  10. #10
    Join Date
    Jul 2003
    Location
    Michigan
    Posts
    1,941
    Quote Originally Posted by mallier
    PRODUCT column is not in table TMPPARTMSTR and it is refered from TMPPRODUCT_MASTER .Obviously condition would be true and deleted all records.
    So, are you saying: because the subquery can't find a column named "product" in the only table in the subquery, it will go OUTSIDE of the subquery to look in other tables contained in the query? That seems counterintuitive to me, but that is what it appears to be doing.
    Last edited by RedNeckGeek; 02-22-06 at 12:55.
    Inspiration Through Fermentation

  11. #11
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    That's a fact JACK

    Look at my SELECT Statement as a sample...

    I thought it only coorelated in the predicate and not in the SELECT
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  12. #12
    Join Date
    Jul 2003
    Location
    Michigan
    Posts
    1,941
    Well, between what I've read here and in BOL, I almost understand whats happening. In the future, I'll stick with the advice given here, and use tablename.columnname, even though BOL doesn't do that in some of their examples.

    Learn something new every time I come here.
    Thanks!
    Last edited by RedNeckGeek; 02-22-06 at 17:36.
    Inspiration Through Fermentation

  13. #13
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Quote Originally Posted by RedNeckGeek
    tablename.columnname, even though BOL doesn't do that in some of their examples.
    ahhh the blind dude way....

    I use aliases instead

    SELECT * FROM Orders o JOIN [Order Details] d ON o.OrderId = d.OrderId

    Mr. dude fully qualifies everything, because, well, he's blind you see
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  14. #14
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    The blind dude codes with style!
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •