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??





Show quoteHide quote

On Sun, 4 Dec 2005 11:38:16 +0200, Islamegy® wrote:

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

SQL Server and Time Zome
SQL Server index low cardinality columns
running stored procedures from SQL Agent
need output where same column has more than one pirce of data
query help - "Most Helpful Review" idea