Deleting duplicate rows from a table without using temporary table

Hi all ,
Can anyone tell me the query to delete the duplicates from a table
CASE_COUNTRY
Country CountryCode
-- --
France FR
France FR
Italy IT
Germany GM
Here France is a duplicate row , i have 10000 records like this
Regards
AlertAdminAlertAdmin wrote:
> Hi all ,
> Can anyone tell me the query to delete the duplicates from a table
> CASE_COUNTRY
> Country CountryCode
> -- --
> France FR
> France FR
> Italy IT
> Germany GM
>
> Here France is a duplicate row , i have 10000 records like this
>
> Regards
> AlertAdmin
You could try the following. I'm not sure what advantage if any that
this would have over using a temp table.
BEGIN TRAN
GO
CREATE TRIGGER trg_case_country ON case_country
FOR DELETE
AS
INSERT INTO case_country (country, countrycode)
SELECT DISTINCT country, countrycode
FROM deleted ;
GO
DELETE FROM case_country ;
DROP TRIGGER trg_case_country;
COMMIT TRAN
GO
The most important step comes next. Add the missing keys:
ALTER TABLE case_country
ADD CONSTRAINT pk_case_country PRIMARY KEY (countrycode);
ALTER TABLE case_country
ADD CONSTRAINT ak1_case_country UNIQUE (country);
Your choice of country code for Germany is unusual. I suggest you use
the ISO standard country codes unless you are working to some unique
standard that requires otherwise:
http://www.iso.ch/iso/en/prods-serv...ex.html

David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--|||AlertAdmin wrote:
> Hi all ,
> Can anyone tell me the query to delete the duplicates from a table
> CASE_COUNTRY
> Country CountryCode
> -- --
> France FR
> France FR
> Italy IT
> Germany GM
>
> Here France is a duplicate row , i have 10000 records like this
>
> Regards
> AlertAdmin
In SQL Server 2005 the following also works for me, although I'm not
certain that this behaviour is well-documented or totally reliable.
CREATE TABLE case_country (country VARCHAR(35) NOT NULL, countrycode
CHAR(2) NOT NULL /* NO KEY! */);
INSERT INTO case_country VALUES ('France', 'FR');
INSERT INTO case_country VALUES ('France', 'FR');
INSERT INTO case_country VALUES ('Italy', 'IT');
INSERT INTO case_country VALUES ('Germany', 'GM');
WITH T AS
(SELECT country, countrycode, ROW_NUMBER()
OVER (PARTITION BY country, countrycode
ORDER BY country, countrycode) AS row_num
FROM case_country)
DELETE FROM T
WHERE row_num>1;
SELECT * FROM case_country;
Result:
country countrycode
-- --
France FR
Italy IT
Germany GM
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--|||Thanks alot , it works fine for me
Regards
AlertAdmin|||And don't forget to add the primary key and unique constraint!
Hope this helps.
Dan Guzman
SQL Server MVP
"AlertAdmin" <sajin@.iprlab.com> wrote in message
news:1141472981.407005.316810@.z34g2000cwc.googlegroups.com...
> Thanks alot , it works fine for me
> Regards
> AlertAdmin
>|||Hi,
I got another solution by adding a identity column
alter table case_country add test_iden int identity(1,1)
delete from case_country where test_iden not in (select min(test_iden)
from tab group by country)
alter table tab drop column test_iden
Regards
AlertAdmin