May 29, 2007 at 9:07 pm
Need help with a query selecting certain records and a statement to delete certain records.
Cleaning up a table with similar records; there are no duplicate records. From the following query I have identified records with a duplicate "KeyID" column (not a primary key). I have left out other column names that are not necessary to review for this question. There will be no more than two duplicate KeyIDs.
SELECT A.KeyID, A.[DateAdd], A.DateClose
FROM All_Title A
INNER JOIN All_Title B
ON A.KeyID = B.KeyID
GROUP BY A.KeyID, A.[DateAdd], A.DateClose
HAVING COUNT(A.KeyID) > 1
ORDER BY A.KeyID
As an example, a partial result set might look like this:
KeyID DateAdd DateClose
1 x y
1 x NULL
2 x y
2 x y
3 x NULL
3 x NULL
4 x y
4 z NULL
Need to delete one record from each set of duplicate KeyIDs where:
1) the DateAdd column is the same for each record
2) DateClose is NULL
2) the other DateClose Column is not NULL
From the example result set above, only the second record would be deleted. It shares a duplicate KeyID, the DateAdd column is the same for both records, DateClose is Null, and DateClose is not NULL for the remaining record.
Would like to see a query to bring records with the same KeyID, same DateAdd, where in that set DateClose is NULL for one record and not NULL for the other. Then a delete statement that only deletes the record where DateClose is NULL as described above.
May 29, 2007 at 9:24 pm
Can you post DDL and sample data?
May 29, 2007 at 9:40 pm
--For this example, I am using a limited set of data and have not included all of the columns.
CREATE TABLE Test (KeyID char(6), [DateAdd] smalldatetime, DateClose smalldatetime, Nothing int IDENTITY (1,1))
SET NOCOUNT ON
INSERT INTO Test (KeyID, [DateAdd], DateClose) VALUES ('820504', '2006-05-08', '2006-06-12')
INSERT INTO Test (KeyID, [DateAdd], DateClose) VALUES ('820504', '2006-05-08', NULL)
INSERT INTO Test (KeyID, [DateAdd], DateClose) VALUES ('820514', '2005-10-12', '2006-06-21')
INSERT INTO Test (KeyID, [DateAdd], DateClose) VALUES ('820514', '2005-10-12', '2006-06-21')
INSERT INTO Test (KeyID, [DateAdd], DateClose) VALUES ('820518', '2006-05-09', NULL)
INSERT INTO Test (KeyID, [DateAdd], DateClose) VALUES ('820518', '2006-05-09', NULL)
INSERT INTO Test (KeyID, [DateAdd], DateClose) VALUES ('820577', '2006-05-10', '2006-12-31')
INSERT INTO Test (KeyID, [DateAdd], DateClose) VALUES ('820577', '2005-06-11', NULL)
INSERT INTO Test (KeyID, [DateAdd], DateClose) VALUES ('820578', '2006-05-09', '2006-06-13')
INSERT INTO Test (KeyID, [DateAdd], DateClose) VALUES ('820579', '2006-05-09', NULL)
INSERT INTO Test (KeyID, [DateAdd], DateClose) VALUES ('820580', '2006-05-09', NULL)
INSERT INTO Test (KeyID, [DateAdd], DateClose) VALUES ('820581', '2006-05-09', NULL)
SET NOCOUNT OFF
SELECT * FROM TEST
--DROP TABLE Test
May 29, 2007 at 10:01 pm
DELETE D
FROM Test D
INNER JOIN Test L ON D.KeyID = L.KeyID AND D.DateAdd = L.DateAdd
WHERE D.DateClose IS NULL AND L.DateClose IS NOT NULL
_____________
Code for TallyGenerator
May 29, 2007 at 10:19 pm
That works great. Thank you!!
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply