Delete Duplicate Record

I have Changed my database schema and transform Data with new keys using
this Query :
SELECT Case_No, Case_Date, Ma7akem_ID, COUNT(*) AS Expr1
FROM Master_Ahkam
GROUP BY Case_No, Case_Date, Ma7akem_ID
HAVING (COUNT(*) > 1)
Due to a Design error in the old one there was a "ModifyDate" in the
primarykey. so the whole record are the same except the Modifydate.. I need
to delete these rows..
Is there anyway to do so'Please post DDL, so that people do not have to guess what the keys,
constraints, Declarative Referential Integrity, data types, etc. in
your schema are. Sample data is also a good idea, along with clear
specifications. It is very hard to debug code when you do not let us
see it.
Let's get back to the basics of an RDBMS. Rows are not records; fields
are not columns; tables are not files. If you had a proper relational
design in the first place, you could not have dups in the first place.
Can you kill the guy that did this? That will improve the overall
quality of your software.|||Hiiiiiiii
The one who designed this is my boss so if I Kill him I 'll Have to Find new
Job, Can you help -;)
Here is the DDL
----
--
CREATE TABLE [AH_Tasneef] (
[ID] [PKInt] NOT NULL ,
[Parent_ID] [PKInt] NOT NULL CONSTRAINT [DF__Tasneef__Parent___0519C6AF]
DEFAULT (0),
[Ma7kama_ID] [PKInt] NOT NULL ,
[Text] [varchar] (200) COLLATE Arabic_BIN NOT NULL ,
[UserID] [int] NULL ,
[LastModify] [datetime] NULL ,
CONSTRAINT [PK_AH_Tasneef] PRIMARY KEY NONCLUSTERED
(
[ID]
) ON [PRIMARY] ,
CONSTRAINT [FK_AH_Tasneef_AH_Ma7akem] FOREIGN KEY
(
[Ma7kama_ID]
) REFERENCES [AH_Ma7akem] (
[ID]
) ON UPDATE CASCADE
) ON [PRIMARY]
GO
CREATE TABLE [AH_TasnFakaraat] (
[Tasneef_ID] [PKInt] NOT NULL ,
[Master_ID] [PKInt] NOT NULL ,
[Fakra_No] [int] NOT NULL ,
[UserID] [int] NULL ,
[LastModify] [datetime] NULL ,
CONSTRAINT [PK_AH_TasnFakaraat] PRIMARY KEY NONCLUSTERED
(
[Tasneef_ID],
[Master_ID],
[Fakra_No]
) ON [PRIMARY]
) ON [PRIMARY]
GO
CREATE TABLE [AH_SubMaster] (
[Master_ID] [int] NOT NULL ,
[Fakra_No] [smallint] NOT NULL ,
[Fakra_Text] [text] COLLATE Arabic_BIN NOT NULL ,
[Tasneef_ID] [PKInt] NULL ,
[UserID] [int] NULL ,
[LastModify] [datetime] NULL ,
CONSTRAINT [MyKey_PK_1] PRIMARY KEY NONCLUSTERED
(
[Master_ID],
[Fakra_No]
) WITH FILLFACTOR = 80 ON [PRIMARY] ,
CONSTRAINT [FK_AH_SubMaster_AH_Master] FOREIGN KEY
(
[Master_ID]
) REFERENCES [AH_Master] (
[ID]
) ON UPDATE CASCADE
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
alter table dbo.AH_TasnFakaraat
add constraint FK_AH_TASNF_REFERENCE_AH_SUBMA foreign key (Master_ID,
Fakra_No)
references dbo.AH_SubMaster (Master_ID, Fakra_No)
go
alter table dbo.AH_TasnFakaraat
add constraint FK_AH_TASNF_REFERENCE_AH_TASNE foreign key (Tasneef_ID)
references dbo.AH_Tasneef (ID)
go
----
--
I have duplicate records in table AH_TasnFakaraat which i need to clean...
how could i do so'
"--CELKO--" <jcelko212@.earthlink.net> wrote in message
news:1133664266.448284.115290@.g47g2000cwa.googlegroups.com...
> Please post DDL, so that people do not have to guess what the keys,
> constraints, Declarative Referential Integrity, data types, etc. in
> your schema are. Sample data is also a good idea, along with clear
> specifications. It is very hard to debug code when you do not let us
> see it.
> Let's get back to the basics of an RDBMS. Rows are not records; fields
> are not columns; tables are not files. If you had a proper relational
> design in the first place, you could not have dups in the first place.
> Can you kill the guy that did this? That will improve the overall
> quality of your software.
>|||On Sun, 4 Dec 2005 11:38:16 +0200, Islamegy wrote:

>Hiiiiiiii
>The one who designed this is my boss so if I Kill him I 'll Have to Find ne
w
>Job, Can you help -;)
Hi Islamegy,
I can't help you killing your boss or finding you a new job, but I might
be able to help with the duplicates.
I'm not sure exactly which columns are duplicated and which are not, nor
how to decide which rows to retain and which to remove, so I'll post a
generic example for you to modify.
CREATE TABLE Test
(Col1 int NOT NULL,
Col2 int NOT NULL,
Col3 int NOT NULL,
CONSTRAINT TestKey PRIMARY KEY (Col1, Col2, Col3)
)
INSERT INTO Test (Col1, Col2, Col3)
SELECT 1, 2, 3
UNION ALL
SELECT 1, 2, 4
go
The primary key should have been (Col1, Col2). This means that the data
above is incorrect. The lowest value for Col3 should be retained, all
others removed. In the end, only the (1, 2, 3) row should survive.
-- Remove unwanted data
DELETE FROM Test
WHERE EXISTS
(SELECT *
FROM Test AS t2
WHERE t2.Col1 = Test.Col1
AND t2.Col2 = Test.Col2
AND t2.Col3 < Test.Col3)
-- Change key
ALTER TABLE Test
DROP CONSTRAINT TestKey
ALTER TABLE Test
ADD CONSTRAINT TestKey PRIMARY KEY (Col1, Col2)
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)