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

    Unanswered: Having trouble with EXISTS

    I'm trying to delete duplicate records from a table.
    The table is full of "new" records that need to be added to the database
    with some user input. Since the table is populated from multiple sources,
    duplicate entries can be put in. I'd like to clean those out after each "run".

    Here's what I have so far, but of course, it's not right:

    Code:
    create table tmpKickouts (
    	KICK_ID	INTEGER IDENTITY,
    	PARTCODE	VARCHAR(10),
    	KICKOUT_REASON  INTEGER
    )
    INSERT INTO TMPKICKOUTS ( PARTCODE, KICKOUT_REASON)
    SELECT 'ABC', 1
    UNION ALL
    SELECT 'ABC', 1
    UNION ALL
    SELECT 'ABC', 2
    UNION ALL
    SELECT 'DEF', 2
    UNION ALL 
    SELECT 'GHI', 1
    UNION ALL
    SELECT 'GHI', 1
    UNION ALL
    SELECT 'GHI', 1
    
    SELECT * FROM TMPKICKOUTS TK
    WHERE NOT EXISTS (
    SELECT MIN(KICK_ID) AS KICK_ID, PARTCODE FROM TMPKICKOUTS 
    WHERE PARTCODE IN (
    	SELECT DISTINCT PARTCODE FROM TMPKICKOUTS
    	WHERE KICKOUT_REASON=1)
    AND KICKOUT_REASON=1 AND TMPKICKOUTS.KICK_ID=TK.KICK_ID AND TMPKICKOUTS.PARTCODE=TK.PARTCODE
    GROUP BY PARTCODE)
    AND KICKOUT_REASON=1
    
    
    DROP TABLE TMPKICKOUTS
    In this example, I'd want to delete records 2, 6, & 7

    TIA
    Mark
    Inspiration Through Fermentation

  2. #2
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    What user input?
    What is a "run".
    Duplicate in the input table, or duplicate in the output table?
    Why are you deleting records? Why not just insert distinct records?

    You are going to need to do a lot better job at explaining requirements than that...
    If it's not practically useful, then it's practically useless.

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

  3. #3
    Join Date
    Jul 2003
    Location
    Michigan
    Posts
    1,941
    I import files from our legacy system into Sql Server at different intervals during the day. Every time I do this, I validate the data in the files to make
    sure that things like "partcode" exist in the SQL Server database. Anytime
    a record is found that doesn't exist yet, it get dumped into this "kickout" table. My users will run a process from the front end that will ask them for the input so that the new code can be added to the proper tables in Sql Server.

    Since a product can be added to the legacy system, be added to an order,
    be produced, and shipped all within a few hours of one another, I can end
    up with one kickout for each of those imports (orders, production, shipping, and products). I really only need one, not four.
    Inspiration Through Fermentation

  4. #4
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Ok, keeping in mind that you really ought to prevent this kind of problem from happening in the first place by not adding the offending rows to your table, I think that you can still acheive what you want using:
    Code:
    SELECT *
       FROM TMPKICKOUTS AS a
       WHERE  a.KICK_ID = (SELECT Min(z.KICK_ID)
          FROM TMPKICKOUTS AS z
          WHERE  z.PARTCODE = a.PARTCODE
             AND z.KICKOUT_REASON = a.KICKOUT_REASON)
    Note that if you want to have only one row per partcode, you could remove the KICKOUT_REASON from the sub-select.

    -PatP

  5. #5
    Join Date
    Jul 2003
    Location
    Michigan
    Posts
    1,941
    Pat,

    You're right, of course. But, sometimes the band-aid is easier than preventing the injury in the first place. As long as the company never
    hires a true Sql Server guy, my secret is safe!

    Thanks for the help.
    Inspiration Through Fermentation

Posting Permissions

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