asked
remove some rows based on primary key values
we have a table have primary key PID and foreign key CID, now need to clean some rows based on primary key. the table like below: CREATE TABLE #AA ( PID INT , CID INT , PC VARCHAR(10) ) INSERT INTO #AA ( PID, CID, PC ) VALUES ( 1, NULL, 'P' ), ( 3, NULL, 'P' ), ( 4, 1, 'A' ), ( 5, 1, 'A' ), ( 6, 3, 'A' ), ( 7, 2, 'A' ), ( 8, 2, 'A' ), ( 9, 2, 'A' ) the output need to remove the CID not in PID. OUTPUT like: 1,NULL,'P' 3,NULL,'P' 4,1,'A' 5,1,'A' 6,3,'A' Thanks
SELECT * FROM #AA EXCEPT SELECT * FROM #AA WHERE CID not IN (SELECT PID FROM #AA) this works but not a ideal way of doing it
The script in the original answer already works, but here is another version: select a.* from #AA a left join #AA b on a.CID = b.PID where a.CID is null or b.PID is not null; The idea is that the rows with CID is null need to be included even though they don't have a counterpart. The rows with not null values in the CID need to be valid (from the existing PID values). The script produces the following output for the given set of rows in question: PID CID PC ----------- ----------- ---------- 1 NULL P 3 NULL P 4 1 A 5 1 A 6 3 A I am not sure whether this script is cheaper or not, but should be easy enough to test. Hope this helps, Oleg
20 People are following this question.
