SQL Query to delete duplicate records

Hey Guys...
There was a small logical check missed in my automated blog(KuwaitFresh.com). And because of this there occured duplicate records which means duplicate posts! which will be not be accepted by any user :) Here is the query to delete duplicate records in your table...

List Duplicate Rows:

SELECT Field,Count(*)

FROM TABLE

GROUP BY FIELD

HAVING ( COUNT(*) > 1 )

DELETE duplicate Rows:

General Syntax:

DELETE FROM TABLENAME WHERE POSTID in

(SELECT T1.POSTID

from TABLENAME T1, TABLENAME T2

where T1.DUPLICATEFIELD = T2. DUPLICATEFIELD

and T1. UNIQUEFIELD > T2.UNIQUEFIELD)

Example:

DELETE FROM TABLENAME WHERE POSTID in

(SELECT T1.POSTID

from TABLENAME T1, TABLENAME T2

where T1.TITLE = T2.TITLE

and T1.POSTID > T2.POSTID)

Hope It is Helpful :-)

Regards

Fauzi ~ 4Z

How to delete all records from all tables from Database

Dear Reader,

Following is the SQL query used to delete all records from all tables from Database.
This is useful during development scenarios where you need to get rid of all the dummy records and insert a fresh data.

SQL Query: Appropriate comments are provided to explain its operation

/* The Following set of queries is used to delete all records from all tables from your database */

/* Disables Referential integrity */

/*---------------------------------*/

EXEC sp_MSForEachTable 'ALTER TABLE ? NOCHECK CONSTRAINT ALL'

GO

/* Delete records from tables */

/*----------------------------*/

EXEC sp_MSForEachTable '

IF OBJECTPROPERTY(object_id(''?''), ''TableHasForeignRef'') = 1

DELETE FROM ?

else

TRUNCATE TABLE ?

'

GO

/* Enables Referential integrity */

/*-------------------------------*/

EXEC sp_MSForEachTable 'ALTER TABLE ? CHECK CONSTRAINT ALL'

GO

/*Query ot be used if you would like to reseed all table*/

/*------------------------------------------------------*/

EXEC sp_MSForEachTable '

IF OBJECTPROPERTY(object_id(''?''), ''TableHasIdentity'') = 1

DBCC CHECKIDENT (''?'', RESEED, 0)

'

GO

Hope its Helpful

Regards
Fauzi