delete query required in sql server 2000

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2005 » Development » delete query required in sql server 2000

delete query required in sql server 2000

Rate Topic

Display Mode

Topic Options

Author

Message

rajasekhar857

Posted Thursday, August 27, 2009 11:36 PM

Forum Newbie

Group: General Forum Members
Last Login: Tuesday, September 08, 2009 3:53 AM
Points: 6, Visits: 18

hi my delete query is working fine qith sql server 2005.please help me out giving query in sql server 2000.

DELETE D
FROM
(

SELECT *
,ROW_NUMBER() OVER (PARTITION BY problem_code, patient_id ORDER BY PROBLEM_ID DESC) AS RowNum
FROM EMRPATIENTPROBLEMS
) D
WHERE RowNum > 1
GO

Post #778898

Mahesh Bote

Posted Monday, August 31, 2009 6:50 AM

Ten Centuries

Group: General Forum Members
Last Login: Thursday, February 21, 2013 6:22 AM
Points: 1,074, Visits: 1,205

It seems that you are trying to delete duplicate records. What you have to do is to write a query in SQL Server 2K to return the duplicate records using HAVING clause and then delete those records.

Let us know on this,

Mahesh

MH-09-AM-8694

Post #779856

Slick84

Posted Monday, August 31, 2009 7:26 AM

SSC-Addicted

Group: General Forum Members
Last Login: Monday, October 31, 2011 1:10 PM
Points: 480, Visits: 1,163

Check the below article out. It gives a nice explanation along with SQL 2000 and 2005 syntax and query idea's.

http://code.msdn.microsoft.com/SQLExamples/Wiki/View.aspx?title=DuplicateRows

Thanks,
S

--
Hehe

Post #779885

ChiragNS

Posted Tuesday, September 01, 2009 5:57 AM

SSCrazy

Group: General Forum Members
Last Login: Wednesday, April 24, 2013 5:02 AM
Points: 2,365, Visits: 1,825

You can do a select distinct and insert the records in a new table , drop the old table and rename the new table to the original table. You can do this only if the table contains small or moderate amount of data. Make sure the new table contains all indexes, constraints etc.

"Keep Trying"

Post #780600

« Prev Topic | Next Topic »

Permissions