Delete Query SQL?

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

--
Kahuna
------------

hi,
Kahuna wrote:
> 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))

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....)

--
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz http://www.hotelsole.com
DbaMgr2k ver 0.21.1 - DbaMgr ver 0.65.1 and further SQL Tools
--------- remove DMO to reply