Results 1 to 4 of 4
  1. #1
    Join Date
    Mar 2003
    Posts
    97

    Unanswered: Deleting with multiple criteria

    I have a table with a record that looks like the attached TXT.


    I need to keep the most recently entered value where flag_out = 1 and delete those duplicate
    records, and this should only apply to records where there's also an flag_in value of 1.

    I've tried a bunch of delete statements without avail....

    TIA
    Attached Files Attached Files

  2. #2
    Join Date
    Oct 2003
    Posts
    268
    How do you define a duplicate that you want to delete? Just duplicate contact_id?

    Post your failed SQL attempt so I can see where you are trying to go?

  3. #3
    Join Date
    Mar 2003
    Posts
    97

    Angry

    Unfortunately, I've trashed all my SQL files for this thing..

    But here's some pseudo-code

    1. Get all contact_id's
    2. "Loop" over them and check which "ID" has a record with a contact_id matching #1 AND with a flag_in = 1 and flag_out = 1
    3. If rowcount from #2 > 0, then delete all flag_in's for that contact_id, and keep a unique record with flag_out = 1 (unique = max(id) )

    thnx

  4. #4
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    OK...lots of assumptions with this.....I'm assuming [id] is an identity column to help us know which one was the last one entered...

    Code:
    USE Northwind
    GO
    
    CREATE TABLE myTable99([id] int, contact_id int, flag_in int, flag_out int, rec_id int, val_id int, date_entered datetime)
    GO
    
    INSERT INTO myTable99([id], contact_id, flag_in, flag_out, rec_id, val_id, date_entered)
    SELECT 98993, 		98983, 		0,	1, 	NULL, 		NULL, 	'2004-08-18 10:54:19.630' UNION ALL
    SELECT 330337, 		98983, 		0, 	1, 	NULL, 		NULL, 	'2004-08-18 10:54:19.630' UNION ALL
    SELECT 390898, 		98983, 		0, 	1, 	NULL, 		NULL, 	'2004-08-18 10:54:19.630' UNION ALL
    SELECT 406908, 		98983, 		1, 	0, 	NULL, 		NULL, 	'2004-08-23 22:38:10.327' UNION ALL
    SELECT 466799, 		98983, 		1, 	0, 	NULL, 		NULL, 	'2004-08-24 00:44:39.640' UNION ALL
    SELECT 98993, 		98984, 		0,	1, 	NULL, 		NULL, 	'2004-08-18 10:54:19.630' UNION ALL
    SELECT 330337, 		98984, 		0, 	1, 	NULL, 		NULL, 	'2004-08-18 10:54:19.630' UNION ALL
    SELECT 390898, 		98984, 		0, 	1, 	NULL, 		NULL, 	'2004-08-18 10:54:19.630' UNION ALL
    SELECT 406908, 		98984, 		0, 	1, 	NULL, 		NULL, 	'2004-08-23 22:38:10.327' UNION ALL
    SELECT 466799, 		98984, 		1, 	0, 	NULL, 		NULL, 	'2004-08-24 00:44:39.640'
    GO
    
    SELECT * FROM myTable99
    
    SELECT * INTO myTable99_New FROM (
      SELECT l.* 
        FROM myTable99 l
        JOIN (
    	  SELECT contact_id, MAX([id]) AS MAX_id
    	    FROM myTable99 i
    	   WHERE flag_out = 1
    	GROUP BY contact_id
    	  ) AS r
          ON l.contact_id = r.contact_id
         AND l.[id] = r.MAX_id
    UNION ALL
       SELECT *
         FROM myTable99 
        WHERE flag_out <> 1
    ) AS xxx
    
    TRUNCATE TABLE myTable99
    
    INSERT INTO myTable99 SELECT * FROM myTable99_New
    
    SELECT * FROM myTable99
    GO
    
    DROP TABLE myTable99
    DROP TABLE myTable99_New
    GO
    But damn it, I thought we could correlate a delete
    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.

Posting Permissions

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