deleting duplicate records in atable

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

deleting duplicate records in atable

Author

Topic

rajasekhar857
Constraint Violating Yak Guru

396 Posts

Posted - 2009-09-25 : 01:32:26

hi iam having atable EMRLicensedRoleInfo having three columns
EMR_GROUP_ID varchar
ROLE_NAME varchar
STATUS numeric

iam having totally 845 records in it bit lot of duplicates in it.
like
SELECT emr_group_id,count(*)
FROM EMRLicensedRoleInfo

GROUP BY emr_group_id
HAVING count(*) > 1

now i want to remove duplicates frpm that table.what is the query in doing so

rajasekhar857
Constraint Violating Yak Guru

396 Posts

Posted - 2009-09-25 : 02:06:11

GOT it just given select distinct * into table1 from EMRLicensedRoleInfo and drop the old table and passed new vales into the old table

Nageswar9
Aged Yak Warrior

600 Posts

Posted - 2009-09-25 : 06:10:53

Hi, Take the backup for table and use following query

delete t from
( select row_number() over (partition by emr_group_id order by emr_group_id ) as rid from table ) t
where t.rid > 1

select * from table

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-09-25 : 14:28:56

quote:
Originally posted by Nageswar9

Hi, Take the backup for table and use following query

delete t from
( select row_number() over (partition by emr_group_id order by emr_group_id ) as rid from table ) t
where t.rid > 1

select * from table

you cant take a backup of single table alone

Nageswar9
Aged Yak Warrior

600 Posts

Posted - 2009-09-25 : 23:03:00

Hi,

I mean create another table and insert the data into newtable after delete the records

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources