Page 1 of 2 12 LastLast
Results 1 to 15 of 29
  1. #1
    Join Date
    Jul 2003
    Location
    Michigan
    Posts
    1,941

    Unanswered: Deleting Records

    I have a table with a load of orphaned records (I know... poor design)
    I'm trying to get rid of them, but I'm having a brain cramp.

    I need to delete all the records from the table "Floor_Stock" that
    would be returned by this select statement:

    Code:
    SELECT     FLOOR_STOCK.PRODUCT, FLOOR_STOCK.SITE
    FROM         PRODUCT_MASTER INNER JOIN
             FLOOR_STOCK ON PRODUCT_MASTER.PRODUCT = 
            FLOOR_STOCK.PRODUCT LEFT OUTER JOIN
             BOD_HEADER ON FLOOR_STOCK.PRODUCT = 
             BOD_HEADER.PRODUCT AND FLOOR_STOCK.SITE = 
             BOD_HEADER.SITE
    WHERE     (BOD_HEADER.BOD_INDEX IS NULL) AND 
    (PRODUCT_MASTER.PROD_TYPE IN ('f', 'n', 'k', 'b', 'l', 's'))
    I was thinking along the lines of:

    Code:
    DELETE FROM FLOOR_STOCK INNER JOIN
           (SELECT     FLOOR_STOCK. PRODUCT, FLOOR_STOCK.SITE
             FROM          PRODUCT_MASTER INNER JOIN
             FLOOR_STOCK ON PRODUCT_MASTER. PRODUCT =   
                 FLOOR_STOCK.PRODUCT 
             LEFT OUTER JOIN BOD_HEADER ON FLOOR_STOCK. PRODUCT =
             BOD_HEADER. PRODUCT AND FLOOR_STOCK.SITE = BOD_HEADER.SITE
             WHERE      (BOD_HEADER.BOD_INDEX IS NULL) AND
                (PRODUCT_MASTER.PROD_TYPE IN ('f', 'n', 'k', 'b', 'l', 's'))) F ON 
    FLOOR_STOCK. PRODUCT = F. PRODUCT 
    AND FLOOR_STOCK.SITE = F.SITE
    ... but Sql Server just laughs at me: "Incorrect Syntax near the keyword INNER"
    Inspiration Through Fermentation

  2. #2
    Join Date
    Jun 2003
    Location
    West Palm Beach, FL
    Posts
    2,713

    Cool

    Did you try:
    Code:
    DELETE FROM FLOOR_STOCK 
    WHERE (FLOOR_STOCK.PRODUCT, FLOOR_STOCK.SITE) IN
           (SELECT  FLOOR_STOCK.PRODUCT, FLOOR_STOCK.SITE
              FROM  PRODUCT_MASTER 
              INNER JOIN FLOOR_STOCK 
                 ON PRODUCT_MASTER. PRODUCT =  FLOOR_STOCK.PRODUCT 
               LEFT OUTER JOIN BOD_HEADER
                 ON FLOOR_STOCK. PRODUCT = BOD_HEADER. PRODUCT
                AND FLOOR_STOCK.SITE = BOD_HEADER.SITE
             WHERE (BOD_HEADER.BOD_INDEX IS NULL) AND
                (PRODUCT_MASTER.PROD_TYPE IN ('f', 'n', 'k', 'b', 'l', 's')))
    The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

  3. #3
    Join Date
    Jul 2003
    Location
    Michigan
    Posts
    1,941
    Sql Server doesn't like that either.
    I didn't think you could use the IN clause on multiple fields.
    Thanks anyway!
    Inspiration Through Fermentation

  4. #4
    Join Date
    Jun 2003
    Location
    West Palm Beach, FL
    Posts
    2,713

    Cool

    You could CONCATENATE the columns:
    Code:
    WHERE FLOOR_STOCK.PRODUCT+FLOOR_STOCK.SITE IN (
    SELECT FLOOR_STOCK.PRODUCT+FLOOR_STOCK.SITE FROM...
    The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

  5. #5
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    if the purpose of the LEFT OUTER join was to find unmatched rows by checking BOD_INDEX IS NULL, then i think this will do it --
    Code:
    delete from FLOOR_STOCK
     where exists
           ( select *
               from PRODUCT_MASTER
              where PRODUCT 
                  = FLOOR_STOCK.PRODUCT 
                and PROD_TYPE 
                 in ('f', 'n', 'k', 'b', 'l', 's')
           )
       and not exists
           ( select *
               from BOD_HEADER 
              where PRODUCT
                  = FLOOR_STOCK.PRODUCT 
                and SITE
                  = FLOOR_STOCK.SITE 
           )
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  6. #6
    Join Date
    Apr 2004
    Location
    Kansas City, MO
    Posts
    734
    Code:
    DELETE FS
    FROM 
    	FLOOR_STOCK FS
    	INNER JOIN (
    		SELECT
    			FS1.PRODUCT, 
    			FS1.SITE
    		FROM
    			PRODUCT_MASTER PM 
    			INNER JOIN FLOOR_STOCK FS1 ON PM.PRODUCT = FS1.PRODUCT 
    			LEFT OUTER JOIN BOD_HEADER BH ON FS1.PRODUCT = BH.PRODUCT 
    				AND FS1.SITE = BH.SITE
             WHERE
    			BH.BOD_INDEX IS NULL 
    			AND PM.PROD_TYPE IN ('f', 'n', 'k', 'b', 'l', 's')) FS2 ON 
    		FS.PRODUCT = FS2.PRODUCT 
    		AND FS.SITE = FS2.SITE
    MeanOldDBA
    derrickleggett@hotmail.com
    When life gives you a lemon, fire the DBA.

  7. #7
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Did you cut and paste directly from your code? Because your second statement (the delete one) had odd spaces inserted in it that would certainly cause syntax errors.

    Does this give you an error, and if so, on what line?

    Code:
    DELETE
    FROM	FLOOR_STOCK
    	INNER JOIN
    		(SELECT	FLOOR_STOCK.PRODUCT,
    			FLOOR_STOCK.SITE
    		FROM	PRODUCT_MASTER
    			INNER JOIN FLOOR_STOCK ON PRODUCT_MASTER.PRODUCT = FLOOR_STOCK.PRODUCT 
    			LEFT OUTER JOIN BOD_HEADER
    				ON FLOOR_STOCK.PRODUCT = BOD_HEADER.PRODUCT
    				AND FLOOR_STOCK.SITE = BOD_HEADER.SITE
    		WHERE	BOD_HEADER.BOD_INDEX IS NULL
    			AND PRODUCT_MASTER.PROD_TYPE IN ('f', 'n', 'k', 'b', 'l', 's')) F
    		ON	FLOOR_STOCK.PRODUCT = F.PRODUCT 
    			AND FLOOR_STOCK.SITE = F.SITE
    If it's not practically useful, then it's practically useless.

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

  8. #8
    Join Date
    Apr 2004
    Location
    Kansas City, MO
    Posts
    734
    Server: Msg 156, Level 15, State 1, Line 3
    Incorrect syntax near the keyword 'INNER'.
    Server: Msg 170, Level 15, State 1, Line 12
    Line 12: Incorrect syntax near 'F'.
    MeanOldDBA
    derrickleggett@hotmail.com
    When life gives you a lemon, fire the DBA.

  9. #9
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Ok. That is what I get for trying to code off the top of my head without QA in front of me. But try this instead:

    Code:
    DELETE	FLOOR_STOCK
    FROM	FLOOR_STOCK
    	INNER JOIN
    		(SELECT	FLOOR_STOCK.PRODUCT,
    			FLOOR_STOCK.SITE
    		FROM	PRODUCT_MASTER
    			INNER JOIN FLOOR_STOCK ON PRODUCT_MASTER.PRODUCT = FLOOR_STOCK.PRODUCT 
    			LEFT OUTER JOIN BOD_HEADER
    				ON FLOOR_STOCK.PRODUCT = BOD_HEADER.PRODUCT
    				AND FLOOR_STOCK.SITE = BOD_HEADER.SITE
    		WHERE	BOD_HEADER.BOD_INDEX IS NULL
    			AND PRODUCT_MASTER.PROD_TYPE IN ('f', 'n', 'k', 'b', 'l', 's')) F
    		ON	FLOOR_STOCK.PRODUCT = F.PRODUCT 
    			AND FLOOR_STOCK.SITE = F.SITE
    According to Books Online, this transact SQL extension should work.
    If it's not practically useful, then it's practically useless.

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

  10. #10
    Join Date
    Apr 2004
    Location
    Kansas City, MO
    Posts
    734
    That will work. If you look at the example I posted, it does basically the same thing using table aliases.
    MeanOldDBA
    derrickleggett@hotmail.com
    When life gives you a lemon, fire the DBA.

  11. #11
    Join Date
    Jul 2003
    Location
    Michigan
    Posts
    1,941
    So many answers to one simple question, I'm so confused! (Just kidding )
    Thanks for the help, folks. I learned a few new tricks that will come in very handy.
    Inspiration Through Fermentation

  12. #12
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    hey, how about some feedback?

    which methods worked?

    i think mine was the prettiest and simplest, but only you can tell us if it was correct
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  13. #13
    Join Date
    Apr 2004
    Location
    Kansas City, MO
    Posts
    734
    i think mine was the prettiest and simplest, but only you can tell us if it was correct
    LOL......NICE rudy!!!!
    MeanOldDBA
    derrickleggett@hotmail.com
    When life gives you a lemon, fire the DBA.

  14. #14
    Join Date
    Jul 2003
    Location
    Michigan
    Posts
    1,941
    Quote Originally Posted by r937
    hey, how about some feedback?

    which methods worked?

    i think mine was the prettiest and simplest, but only you can tell us if it was correct
    Sorry Rudy... I attempted blindman's (and derrick's) solution first, and it worked. It was as simple as I forgot to tell it which table to delete from:
    I had:
    Delete
    from floor_stock...

    And it should have been:
    Delete floor_stock
    from floor_stock...

    However, your solution using EXISTS did help me understand how to use that clause. That's one I've always shied away from.

    You Sql guys sure are territorial
    Inspiration Through Fermentation

  15. #15
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    BOO-YAH!

    Though I must concede that derrick got the solution first.
    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
  •