deleting records that meet specific criteria

[collierd]

Hello

I am using SQL 2000

I have the following code which doesn't quite work:

select * from #missing_supplier_pcsubsupplier msp
where subledid + ' ' + product_code + ' ' + pcsubsupplierid in
(select distinct subledid, product_code, pcsubsupplierid from #missing_supplier_pcsubsupplier
group by subledid, product_code, pcsubsupplierid
having count(*) = 1)

The problem lies in the 'in' part
Is there any easier way to write this
I want to delete these records so I am testing a select first

Thanks

Damian.

 

[bborissov]

select *
       from #missing_supplier_pcsubsupplier msp
INNER JOIN (select subledid, product_code, pcsubsupplierid
                    from #missing_supplier_pcsubsupplier
                    group by subledid, product_code,
                             pcsubsupplierid
                    having count(*) = 1) Tbl1
ON msp.subledid        = Tbl1.subledid          AND
   msp.product_code    = Tbl1.product_code      AND
   msp.pcsubsupplierid = Tbl1.pcsubsupplierid

not tested

Borislav Borissov
VFP9 SP1, SQL Server 2000/2005.
MVP VFP

 

[AlexCuse]

I believe your change would be this:

select * from 
#missing_supplier_pcsubsupplier msp
where 
subledid + ' ' + product_code + ' ' + pcsubsupplierid in
(select distinct 
[COLOR=red[subledid + ' ' + product_code + ' ' + pcsubsupplierid[/color] from #missing_supplier_pcsubsupplier
group by subledid, product_code, pcsubsupplierid
having count(*) = 1)

Note that making this a delete query will delete all of your records. Do you have any kind of date field or ID column that would tell you which of the n records you want to keep?

Hope this helps,

Alex

Professor: But what about your superintelligence?
Gunther: When I had that there was too much pressure to use it. All I want out of life is to be a monkey of moderate intelligence who wears a suit. That's why I've decided to transfer to Business School.
Professor: NOOOOOOOOOOOO.

 

[AlexCuse]

Oops, typo. Line six should be

[COLOR=red]subledid + ' ' + product_code + ' ' + pcsubsupplierid[/color] from

BBorisov's fix will also work for the select, and I'm sure for the eventual delete as well.


Professor: But what about your superintelligence?
Gunther: When I had that there was too much pressure to use it. All I want out of life is to be a monkey of moderate intelligence who wears a suit. That's why I've decided to transfer to Business School.
Professor: NOOOOOOOOOOOO.

 

[collierd]

Thanks for your help
I am going to go down Borislav's route as it seems like the tidy option
Alex, that does fix the problem I had though

Damian.