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)

delete all data and restart key count to 0

How do I clear all the entrys from a table and make the keys start all overIf u need the current records again u can copy the records to some new table drop the identity column and create it again with the proper seed and import the data back from the temp table.|||truncate table will remove all data and reset the IDENTITY counter to the original seed

Deinstall by deleting resistry keys

I have tried to deinstall my SQL Server Enterprise Evaluation Edition so I can reinstall my newly purchased Developer Edition License. I originally tried to upgrade, but the upgrade failed. Attempts to deinstall the from the Windows Add/Delete Programs applet have also failed
I followed the instructions at http://support.microsoft.com/?id=290991 including backing up my system state and deleting regisitry keys. I went as far as I could removing all known instances and using full text removal. When I try to reinstall, my old deleted instance name is the only one that appears in Enterprise Manager and of course I cannot see the instance I tried to install
Deinstalling the instance I just triesd to install, I went back to look at the registry again. I noticed a registry folder under LOCAL_MACHINE>SOFTWARE>MICROSOFT that was named SQL_REDIST containing a folder named 1.00.000 and one named Setup
Should these also be deleted
Thanks!I seem to have reinstalled successfully
I went back through the new install procedure and made sure to name a new instance instead of taking the default instance in the wizard. I named the new instance the exact same name as I had on the Evaluation Edition instance and everything now seems to be working.sql

Deinstall by deleting resistry keys

I have tried to deinstall my SQL Server Enterprise Evaluation Edition so I can reinstall my newly purchased Developer Edition License. I originally tried to upgrade, but the upgrade failed. Attempts to deinstall the from the Windows Add/Delete Programs ap
plet have also failed.
I followed the instructions at http://support.microsoft.com/?id=290991 including backing up my system state and deleting regisitry keys. I went as far as I could removing all known instances and using full text removal. When I try to reinstall, my old del
eted instance name is the only one that appears in Enterprise Manager and of course I cannot see the instance I tried to install.
Deinstalling the instance I just triesd to install, I went back to look at the registry again. I noticed a registry folder under LOCAL_MACHINE>SOFTWARE>MICROSOFT that was named SQL_REDIST containing a folder named 1.00.000 and one named Setup.
Should these also be deleted?
Thanks!
I seem to have reinstalled successfully.
I went back through the new install procedure and made sure to name a new instance instead of taking the default instance in the wizard. I named the new instance the exact same name as I had on the Evaluation Edition instance and everything now seems to b
e working.

Deinstall by deleting resistry keys

I have tried to deinstall my SQL Server Enterprise Evaluation Edition so I c
an reinstall my newly purchased Developer Edition License. I originally trie
d to upgrade, but the upgrade failed. Attempts to deinstall the from the Win
dows Add/Delete Programs ap
plet have also failed.
I followed the instructions at http://support.microsoft.com/?id=290991 inclu
ding backing up my system state and deleting regisitry keys. I went as far a
s I could removing all known instances and using full text removal. When I t
ry to reinstall, my old del
eted instance name is the only one that appears in Enterprise Manager and of
course I cannot see the instance I tried to install.
Deinstalling the instance I just triesd to install, I went back to look at t
he registry again. I noticed a registry folder under LOCAL_MACHINE>SOFTWARE
>MICROSOFT that was named SQL_REDIST containing a folder named 1.00.000 and
one named Setup.
Should these also be deleted?
Thanks!I seem to have reinstalled successfully.
I went back through the new install procedure and made sure to name a new in
stance instead of taking the default instance in the wizard. I named the new
instance the exact same name as I had on the Evaluation Edition instance an
d everything now seems to b
e working.

Defining Foreign keys in Management Studio

Hi,
I am using SQL Server 2005.
I have created the tables for my Database through the Management Studio
front end tool.
I can apply a primary key to the tables easily enough. What I am having
trouble with is assigning multiple columns as primary keys and also how to
define foreign keys between tables.
Does anyone have suggesstions how I can achieve this through management
studio?
Thanks In Advance
MaccaXref: TK2MSFTNGP01.phx.gbl microsoft.public.sqlserver.server:433758
On Wed, 10 May 2006 07:56:02 -0700, Macca wrote:

>Hi,
>I am using SQL Server 2005.
>I have created the tables for my Database through the Management Studio
>front end tool.
>I can apply a primary key to the tables easily enough. What I am having
>trouble with is assigning multiple columns as primary keys
Hi Macca,
Easiest: open a new query window, type
ALTER TABLE MyTable
ADD CONSTRAINT PK_MyTable -- Or any other name
PRIMARY KEY (Col1, Col2, Col3)
Then, click the Execute button.
But if you prefer to use point and cllick, just hold down the
Ctrl-button on your keyboard while selecting key columns, than
right-click and choose "Set Primary Key".

> and also how to
>define foreign keys between tables.
Again, the easiest is to just type and execute the SQL command:
ALTER TABLE ReferingTable
ADD CONSTRAINT LogicalNameGoesHere
FOREIGN KEY (Col1, Col2, Col3)
REFERENCES ReferedTable (Col1, Col2, Col3)
Optionally, add an ON UPDATE and/or ON DELETE clause.
Using point and click: rightclick refering table and choose "Modify".
Click menu-item "Table Designer" / "Relationships". Click "Add". Under
"(General)", find the entry for "Tables and Columns Specification",
click in the emppty field next to it, then click the smalll ellipsis
button. Enter a name for the foreign key constraint. Then, on the right
hand side, use the drop down lists to select the column(s) that form the
relationship. Move to the left-hand side, choose the refered table and
choose the columns that are refered to. Click "OK" to save.
Regardless of whether you use SQL or point and click to set the
relationship, the column(s) in the refered table MUST be set as either a
PRIMARY KEY or a UNIQUE constraint.
Hugo Kornelis, SQL Server MVP|||Hi Hugo,
Thanks for the reply. I have a question with the SQl that creates a foreign
key.
If I have Table1 and Table 2 and Table 1 has a foreign key which is the
Primary key of Table 2. Is Table 1 or Table 2 the referring table in your SQ
L
query?
Thanks
Macca
"Hugo Kornelis" wrote:

> On Wed, 10 May 2006 07:56:02 -0700, Macca wrote:
>
> Hi Macca,
> Easiest: open a new query window, type
> ALTER TABLE MyTable
> ADD CONSTRAINT PK_MyTable -- Or any other name
> PRIMARY KEY (Col1, Col2, Col3)
> Then, click the Execute button.
> But if you prefer to use point and cllick, just hold down the
> Ctrl-button on your keyboard while selecting key columns, than
> right-click and choose "Set Primary Key".
>
> Again, the easiest is to just type and execute the SQL command:
> ALTER TABLE ReferingTable
> ADD CONSTRAINT LogicalNameGoesHere
> FOREIGN KEY (Col1, Col2, Col3)
> REFERENCES ReferedTable (Col1, Col2, Col3)
> Optionally, add an ON UPDATE and/or ON DELETE clause.
> Using point and click: rightclick refering table and choose "Modify".
> Click menu-item "Table Designer" / "Relationships". Click "Add". Under
> "(General)", find the entry for "Tables and Columns Specification",
> click in the emppty field next to it, then click the smalll ellipsis
> button. Enter a name for the foreign key constraint. Then, on the right
> hand side, use the drop down lists to select the column(s) that form the
> relationship. Move to the left-hand side, choose the refered table and
> choose the columns that are refered to. Click "OK" to save.
> Regardless of whether you use SQL or point and click to set the
> relationship, the column(s) in the refered table MUST be set as either a
> PRIMARY KEY or a UNIQUE constraint.
> --
> Hugo Kornelis, SQL Server MVP
>|||> If I have Table1 and Table 2 and Table 1 has a foreign key which is the
> Primary key of Table 2. Is Table 1 or Table 2 the referring table in your
SQL
> query?
It is not the query that describes which table is the referencing or the ref
erenced table. It is the
data model. In the data model you describe, Table2 is the referenced table a
nd Table 1 is the
referencing table.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Macca" <Macca@.discussions.microsoft.com> wrote in message
news:F44BB119-F24C-4146-91CB-0586510C1589@.microsoft.com...[vbcol=seagreen]
> Hi Hugo,
> Thanks for the reply. I have a question with the SQl that creates a foreig
n
> key.
> If I have Table1 and Table 2 and Table 1 has a foreign key which is the
> Primary key of Table 2. Is Table 1 or Table 2 the referring table in your
SQL
> query?
> Thanks
> Macca
> "Hugo Kornelis" wrote:
>

Defining Foreign keys in Management Studio

Hi,
I am using SQL Server 2005.
I have created the tables for my Database through the Management Studio
front end tool.
I can apply a primary key to the tables easily enough. What I am having
trouble with is assigning multiple columns as primary keys and also how to
define foreign keys between tables.
Does anyone have suggesstions how I can achieve this through management
studio?
Thanks In Advance
MaccaOn Wed, 10 May 2006 07:56:02 -0700, Macca wrote:
>Hi,
>I am using SQL Server 2005.
>I have created the tables for my Database through the Management Studio
>front end tool.
>I can apply a primary key to the tables easily enough. What I am having
>trouble with is assigning multiple columns as primary keys
Hi Macca,
Easiest: open a new query window, type
ALTER TABLE MyTable
ADD CONSTRAINT PK_MyTable -- Or any other name
PRIMARY KEY (Col1, Col2, Col3)
Then, click the Execute button.
But if you prefer to use point and cllick, just hold down the
Ctrl-button on your keyboard while selecting key columns, than
right-click and choose "Set Primary Key".
> and also how to
>define foreign keys between tables.
Again, the easiest is to just type and execute the SQL command:
ALTER TABLE ReferingTable
ADD CONSTRAINT LogicalNameGoesHere
FOREIGN KEY (Col1, Col2, Col3)
REFERENCES ReferedTable (Col1, Col2, Col3)
Optionally, add an ON UPDATE and/or ON DELETE clause.
Using point and click: rightclick refering table and choose "Modify".
Click menu-item "Table Designer" / "Relationships". Click "Add". Under
"(General)", find the entry for "Tables and Columns Specification",
click in the emppty field next to it, then click the smalll ellipsis
button. Enter a name for the foreign key constraint. Then, on the right
hand side, use the drop down lists to select the column(s) that form the
relationship. Move to the left-hand side, choose the refered table and
choose the columns that are refered to. Click "OK" to save.
Regardless of whether you use SQL or point and click to set the
relationship, the column(s) in the refered table MUST be set as either a
PRIMARY KEY or a UNIQUE constraint.
--
Hugo Kornelis, SQL Server MVP|||Hi Hugo,
Thanks for the reply. I have a question with the SQl that creates a foreign
key.
If I have Table1 and Table 2 and Table 1 has a foreign key which is the
Primary key of Table 2. Is Table 1 or Table 2 the referring table in your SQL
query?
Thanks
Macca
"Hugo Kornelis" wrote:
> On Wed, 10 May 2006 07:56:02 -0700, Macca wrote:
> >Hi,
> >
> >I am using SQL Server 2005.
> >
> >I have created the tables for my Database through the Management Studio
> >front end tool.
> >
> >I can apply a primary key to the tables easily enough. What I am having
> >trouble with is assigning multiple columns as primary keys
> Hi Macca,
> Easiest: open a new query window, type
> ALTER TABLE MyTable
> ADD CONSTRAINT PK_MyTable -- Or any other name
> PRIMARY KEY (Col1, Col2, Col3)
> Then, click the Execute button.
> But if you prefer to use point and cllick, just hold down the
> Ctrl-button on your keyboard while selecting key columns, than
> right-click and choose "Set Primary Key".
> > and also how to
> >define foreign keys between tables.
> Again, the easiest is to just type and execute the SQL command:
> ALTER TABLE ReferingTable
> ADD CONSTRAINT LogicalNameGoesHere
> FOREIGN KEY (Col1, Col2, Col3)
> REFERENCES ReferedTable (Col1, Col2, Col3)
> Optionally, add an ON UPDATE and/or ON DELETE clause.
> Using point and click: rightclick refering table and choose "Modify".
> Click menu-item "Table Designer" / "Relationships". Click "Add". Under
> "(General)", find the entry for "Tables and Columns Specification",
> click in the emppty field next to it, then click the smalll ellipsis
> button. Enter a name for the foreign key constraint. Then, on the right
> hand side, use the drop down lists to select the column(s) that form the
> relationship. Move to the left-hand side, choose the refered table and
> choose the columns that are refered to. Click "OK" to save.
> Regardless of whether you use SQL or point and click to set the
> relationship, the column(s) in the refered table MUST be set as either a
> PRIMARY KEY or a UNIQUE constraint.
> --
> Hugo Kornelis, SQL Server MVP
>|||> If I have Table1 and Table 2 and Table 1 has a foreign key which is the
> Primary key of Table 2. Is Table 1 or Table 2 the referring table in your SQL
> query?
It is not the query that describes which table is the referencing or the referenced table. It is the
data model. In the data model you describe, Table2 is the referenced table and Table 1 is the
referencing table.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Macca" <Macca@.discussions.microsoft.com> wrote in message
news:F44BB119-F24C-4146-91CB-0586510C1589@.microsoft.com...
> Hi Hugo,
> Thanks for the reply. I have a question with the SQl that creates a foreign
> key.
> If I have Table1 and Table 2 and Table 1 has a foreign key which is the
> Primary key of Table 2. Is Table 1 or Table 2 the referring table in your SQL
> query?
> Thanks
> Macca
> "Hugo Kornelis" wrote:
>> On Wed, 10 May 2006 07:56:02 -0700, Macca wrote:
>> >Hi,
>> >
>> >I am using SQL Server 2005.
>> >
>> >I have created the tables for my Database through the Management Studio
>> >front end tool.
>> >
>> >I can apply a primary key to the tables easily enough. What I am having
>> >trouble with is assigning multiple columns as primary keys
>> Hi Macca,
>> Easiest: open a new query window, type
>> ALTER TABLE MyTable
>> ADD CONSTRAINT PK_MyTable -- Or any other name
>> PRIMARY KEY (Col1, Col2, Col3)
>> Then, click the Execute button.
>> But if you prefer to use point and cllick, just hold down the
>> Ctrl-button on your keyboard while selecting key columns, than
>> right-click and choose "Set Primary Key".
>> > and also how to
>> >define foreign keys between tables.
>> Again, the easiest is to just type and execute the SQL command:
>> ALTER TABLE ReferingTable
>> ADD CONSTRAINT LogicalNameGoesHere
>> FOREIGN KEY (Col1, Col2, Col3)
>> REFERENCES ReferedTable (Col1, Col2, Col3)
>> Optionally, add an ON UPDATE and/or ON DELETE clause.
>> Using point and click: rightclick refering table and choose "Modify".
>> Click menu-item "Table Designer" / "Relationships". Click "Add". Under
>> "(General)", find the entry for "Tables and Columns Specification",
>> click in the emppty field next to it, then click the smalll ellipsis
>> button. Enter a name for the foreign key constraint. Then, on the right
>> hand side, use the drop down lists to select the column(s) that form the
>> relationship. Move to the left-hand side, choose the refered table and
>> choose the columns that are refered to. Click "OK" to save.
>> Regardless of whether you use SQL or point and click to set the
>> relationship, the column(s) in the refered table MUST be set as either a
>> PRIMARY KEY or a UNIQUE constraint.
>> --
>> Hugo Kornelis, SQL Server MVP

define unique keys

I have a primary key (column name is emp_id) in employee table. Also,
I would like to make a combination of other two columns is unique.
(combination of officecode field and claimno field must be unique).
how can I implement this uniquess in ms sql 2000? thank you.On 17 Oct 2007 13:41:28 -0700, TGEAR wrote:

Quote:

Originally Posted by

>I have a primary key (column name is emp_id) in employee table. Also,
>I would like to make a combination of other two columns is unique.
>(combination of officecode field and claimno field must be unique).
>how can I implement this uniquess in ms sql 2000? thank you.

Hi TGEAR,

CREATE TABLE Demo
(PrimaryKeyColumn int NOT NULL
,HalfOfOtherKeyColumn int NOT NULL
,OtherHalfOfOtherKey int NOT NULL
,SomeOhterKeyForFun int NOT NULL
,CONSTRAINT pk_Demo PRIMARY KEY (PrimaryKeyColumn)
,CONSTRAINT uq_Demo UNIQUE (HalfOfOtherKeyColumn, OtherHalfOfOtherKey)
);

--
Hugo Kornelis, SQL Server MVP
My SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis|||TGEAR wrote:

Quote:

Originally Posted by

I have a primary key (column name is emp_id) in employee table. Also,
I would like to make a combination of other two columns is unique.
(combination of officecode field and claimno field must be unique).
how can I implement this uniquess in ms sql 2000? thank you.

alter table employee
add constraint officecode_claimno
unique (officecode, claimno)