Delete Query SQL?

[Kahuna]

Hi Folks

Changing some legacy data around to make it compatible to a new system and
need to delete a bunch of duplicate records.

Got the select working here:

SELECT fc_main1, COUNT(fc_main1) AS No_Recs, cd_complex, cd_plant,
ad_analysis_code, MIN(cd_id) AS MinRecNo
FROM component_detail_copy
GROUP BY fc_main1, cd_complex, cd_plant, ad_analysis_code
HAVING (ad_analysis_code >= '21') AND (COUNT(fc_main1) > 1) OR
(ad_analysis_code = '01') AND (COUNT(fc_main1) > 1)

Shows a count of the number of records in the duplicate stack and filters
somewhat.

I thought I had nothing better to do than simply add 'WHERE cd_id NOT IN' to
my select string and a few brackets to get this working is SQLServer
Express, but it's complaining about 'Only one expression without EXISTS' or
some such.

What would I need to do to this string to have it run?

DELETE FROM component_detail_copy
WHERE cd_id NOT IN
(SELECT fc_main1, COUNT(fc_main1) AS No_Recs, cd_complex, cd_plant,
ad_analysis_code, MIN(cd_id) AS MinRecNo
FROM component_detail_copy
GROUP BY fc_main1, cd_complex, cd_plant, ad_analysis_code
HAVING (ad_analysis_code >= '21') AND (COUNT(fc_main1) > 1) OR
(ad_analysis_code = '01') AND (COUNT(fc_main1) > 1))

Appreciate any feedback.

TIA

[Andrea Montanari]

hi,

Post by Kahuna
Hi Folks
Changing some legacy data around to make it compatible to a new
system and need to delete a bunch of duplicate records.
SELECT fc_main1, COUNT(fc_main1) AS No_Recs, cd_complex, cd_plant,
ad_analysis_code, MIN(cd_id) AS MinRecNo
FROM component_detail_copy
GROUP BY fc_main1, cd_complex, cd_plant, ad_analysis_code
HAVING (ad_analysis_code >= '21') AND (COUNT(fc_main1) > 1) OR
(ad_analysis_code = '01') AND (COUNT(fc_main1) > 1)
Shows a count of the number of records in the duplicate stack and
filters somewhat.
I thought I had nothing better to do than simply add 'WHERE cd_id NOT
IN' to my select string and a few brackets to get this working is
SQLServer Express, but it's complaining about 'Only one expression
without EXISTS' or some such.
What would I need to do to this string to have it run?
DELETE FROM component_detail_copy
WHERE cd_id NOT IN
(SELECT fc_main1, COUNT(fc_main1) AS No_Recs, cd_complex,
cd_plant, ad_analysis_code, MIN(cd_id) AS MinRecNo
FROM component_detail_copy
GROUP BY fc_main1, cd_complex, cd_plant, ad_analysis_code
HAVING (ad_analysis_code >= '21') AND (COUNT(fc_main1) > 1) OR
(ad_analysis_code = '01') AND (COUNT(fc_main1) > 1))

usually you have to provide the required DDL and some INSERT INTO in order
to set up a repro scenario so that other people do not have to guess all
that..
anyway, try returning only the relative "id"s to be considered in the NOT IN
clause.. so, instead of
WHERE cd_id NOT IN
(SELECT fc_main1, COUNT(fc_main1) AS No_Recs, cd_complex,
cd_plant, ad_analysis_code, MIN(cd_id) AS MinRecNo.

try returning the ids only, similar to
WHERE cd_id NOT IN
(SELECT [ids to be compared with cd_id] FROM....)