Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

delete query required in sql server 2000 Expand / Collapse
Author
Message
Posted Thursday, August 27, 2009 11:36 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, September 8, 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
Posted Monday, August 31, 2009 6:50 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Friday, August 29, 2014 2:57 AM
Points: 1,093, Visits: 1,222
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
Posted Monday, August 31, 2009 7:26 AM


SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-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


--
Post #779885
Posted Tuesday, September 1, 2009 5:57 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 8:08 AM
Points: 2,365, Visits: 1,846
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 »

Add to briefcase

Permissions Expand / Collapse