x

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

more ▼

asked Jan 23 at 03:03 AM in Default

avatar image

SSGC
196 13 20 25

What have you tried so far and the results?

Jan 23 at 03:04 PM sjimmo

do you have solution for it?

Jan 31 at 03:09 PM SSGC
(comments are locked)
10|1200 characters needed characters left

2 answers: sort voted first

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

more ▼

answered Apr 20 at 09:19 PM

avatar image

gdk
25 3

(comments are locked)
10|1200 characters needed characters left

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

more ▼

answered Apr 24 at 02:02 PM

avatar image

Oleg
18.6k 3 7 28

(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x1089
x43
x42
x19

asked: Jan 23 at 03:03 AM

Seen: 86 times

Last Updated: Apr 24 at 02:02 PM

Copyright 2017 Redgate Software. Privacy Policy