Delete Duplicate

SQL 2K
I got a table 'DptAct' having 3 million rows with columns - ID, DPTID, NAME,
SUPVR, MOD_DATE
I want to retain just one row for unique (ID, DPTID) combination, doesn't
matter with values of rest of the columns.
Thanks In Advance
John BHi
Maybe if mod_date is unique for each id,dptid then
DELETE FROM DptAct
WHERE EXISTS ( SELECT * FROM DptAct d where d.id = DptAct.id and
DptAct.DPTID = d.DPTID and DptAct.Mod_Date > d.Mod_date)
John
"MS User" wrote:

> SQL 2K
> I got a table 'DptAct' having 3 million rows with columns - ID, DPTID, NAM
E,
> SUPVR, MOD_DATE
> I want to retain just one row for unique (ID, DPTID) combination, doesn't
> matter with values of rest of the columns.
> Thanks In Advance
> John B
>
>
>|||What does the DDL (i.e. Create Table statement) look like? What is the prima
ry
key on the table?
Thomas
"MS User" <sqlman@.sql.com> wrote in message
news:%23l$$uYhYFHA.3220@.TK2MSFTNGP14.phx.gbl...
> SQL 2K
> I got a table 'DptAct' having 3 million rows with columns - ID, DPTID, NAM
E,
> SUPVR, MOD_DATE
> I want to retain just one row for unique (ID, DPTID) combination, doesn't
> matter with values of rest of the columns.
> Thanks In Advance
> John B
>
>
>|||One of the ways:
http://support.microsoft.com/defaul...kb;en-us;139444
Regards.
"MS User" wrote:

> SQL 2K
> I got a table 'DptAct' having 3 million rows with columns - ID, DPTID, NAM
E,
> SUPVR, MOD_DATE
> I want to retain just one row for unique (ID, DPTID) combination, doesn't
> matter with values of rest of the columns.
> Thanks In Advance
> John B
>
>
>|||INF: How to Remove Duplicate Rows From a Table
http://support.microsoft.com/defaul...444&Product=sql
AMB
"MS User" wrote:

> SQL 2K
> I got a table 'DptAct' having 3 million rows with columns - ID, DPTID, NAM
E,
> SUPVR, MOD_DATE
> I want to retain just one row for unique (ID, DPTID) combination, doesn't
> matter with values of rest of the columns.
> Thanks In Advance
> John B
>
>
>|||In my case , mod_date is not unique.
Let me be more clear, actually I copied table 'DptAct' from different
databases and trying to make a master 'DptAct' with Primary key (ID, DPTID).
Here are the steps I did
1> Created a table DptAct_All with all columns and WITHOUT PrimaryKey
2> Copied DptAct into 'DptAct_All' from all databases
3> Now I need to get data for all the unique (ID, DPTID)
Hope I am clear
Thanks for your time
John B
"John Bell" <jbellnewsposts@.h0tmail.com> wrote in message
news:053E1D63-B76C-4E16-A0C7-66EFC55E95C7@.microsoft.com...
> Hi
> Maybe if mod_date is unique for each id,dptid then
> DELETE FROM DptAct
> WHERE EXISTS ( SELECT * FROM DptAct d where d.id = DptAct.id and
> DptAct.DPTID = d.DPTID and DptAct.Mod_Date > d.Mod_date)
> John
> "MS User" wrote:
>|||John,
Hopefully you now understand WHY it's such a stupid idea to have an "ID" and
a "DEPTID" column. If DEPTID uniquley identifies the row, then MAKE IT THE
PRIMARY KEY!
Also, in SQL Server, we do not have tight naming limitations -- it is OK to
use vowels and words > 8 chars. People wh come around after words will
actually be able to understand things then!
Alex Papadimoulis
http://weblogs.asp.net/Alex_Papadimoulis
"MS User" wrote:

> SQL 2K
> I got a table 'DptAct' having 3 million rows with columns - ID, DPTID, NAM
E,
> SUPVR, MOD_DATE
> I want to retain just one row for unique (ID, DPTID) combination, doesn't
> matter with values of rest of the columns.
> Thanks In Advance
> John B
>
>
>|||Hi
If you have a primary key on the original table then you could insert only
the rows that do not exist from your other databases. If your databases are
on the same server you can use three part names or if you create a linked
server you can use four part naming to access the second table.
Using three part names for another database (Otherdb) on the same server:
INSERT INTO DptAct ( id, deptid, col1, col2 )
SELECT id, deptid, col1, col2 FROM Otherdb..DptAct O
WHERE NOT EXISTS ( SELECT * FROM DptAct D WHERE O.od = D.Id and O.DEPTID =
d.DeptId )
col1 and col2 are example columns and should be replaced by the actual
column names.
John
"MS User" wrote:

> In my case , mod_date is not unique.
> Let me be more clear, actually I copied table 'DptAct' from different
> databases and trying to make a master 'DptAct' with Primary key (ID, DPTID
).
> Here are the steps I did
> 1> Created a table DptAct_All with all columns and WITHOUT PrimaryKey
> 2> Copied DptAct into 'DptAct_All' from all databases
> 3> Now I need to get data for all the unique (ID, DPTID)
> Hope I am clear
> Thanks for your time
> John B
>
>
> "John Bell" <jbellnewsposts@.h0tmail.com> wrote in message
> news:053E1D63-B76C-4E16-A0C7-66EFC55E95C7@.microsoft.com...
>
>sql

Delete Duplicate

SQL 2K
I got a table 'DptAct' having 3 million rows with columns - ID, DPTID, NAME,
SUPVR, MOD_DATE
I want to retain just one row for unique (ID, DPTID) combination, doesn't
matter with values of rest of the columns.
Thanks In Advance
John B
Hi
Maybe if mod_date is unique for each id,dptid then
DELETE FROM DptAct
WHERE EXISTS ( SELECT * FROM DptAct d where d.id = DptAct.id and
DptAct.DPTID = d.DPTID and DptAct.Mod_Date > d.Mod_date)
John
"MS User" wrote:

> SQL 2K
> I got a table 'DptAct' having 3 million rows with columns - ID, DPTID, NAME,
> SUPVR, MOD_DATE
> I want to retain just one row for unique (ID, DPTID) combination, doesn't
> matter with values of rest of the columns.
> Thanks In Advance
> John B
>
>
>
|||What does the DDL (i.e. Create Table statement) look like? What is the primary
key on the table?
Thomas
"MS User" <sqlman@.sql.com> wrote in message
news:%23l$$uYhYFHA.3220@.TK2MSFTNGP14.phx.gbl...
> SQL 2K
> I got a table 'DptAct' having 3 million rows with columns - ID, DPTID, NAME,
> SUPVR, MOD_DATE
> I want to retain just one row for unique (ID, DPTID) combination, doesn't
> matter with values of rest of the columns.
> Thanks In Advance
> John B
>
>
>
|||One of the ways:
http://support.microsoft.com/default...b;en-us;139444
Regards.
"MS User" wrote:

> SQL 2K
> I got a table 'DptAct' having 3 million rows with columns - ID, DPTID, NAME,
> SUPVR, MOD_DATE
> I want to retain just one row for unique (ID, DPTID) combination, doesn't
> matter with values of rest of the columns.
> Thanks In Advance
> John B
>
>
>
|||INF: How to Remove Duplicate Rows From a Table
http://support.microsoft.com/default...44&Product=sql
AMB
"MS User" wrote:

> SQL 2K
> I got a table 'DptAct' having 3 million rows with columns - ID, DPTID, NAME,
> SUPVR, MOD_DATE
> I want to retain just one row for unique (ID, DPTID) combination, doesn't
> matter with values of rest of the columns.
> Thanks In Advance
> John B
>
>
>
|||In my case , mod_date is not unique.
Let me be more clear, actually I copied table 'DptAct' from different
databases and trying to make a master 'DptAct' with Primary key (ID, DPTID).
Here are the steps I did
1> Created a table DptAct_All with all columns and WITHOUT PrimaryKey
2> Copied DptAct into 'DptAct_All' from all databases
3> Now I need to get data for all the unique (ID, DPTID)
Hope I am clear
Thanks for your time
John B
"John Bell" <jbellnewsposts@.h0tmail.com> wrote in message
news:053E1D63-B76C-4E16-A0C7-66EFC55E95C7@.microsoft.com...[vbcol=seagreen]
> Hi
> Maybe if mod_date is unique for each id,dptid then
> DELETE FROM DptAct
> WHERE EXISTS ( SELECT * FROM DptAct d where d.id = DptAct.id and
> DptAct.DPTID = d.DPTID and DptAct.Mod_Date > d.Mod_date)
> John
> "MS User" wrote:
|||John,
Hopefully you now understand WHY it's such a stupid idea to have an "ID" and
a "DEPTID" column. If DEPTID uniquley identifies the row, then MAKE IT THE
PRIMARY KEY!
Also, in SQL Server, we do not have tight naming limitations -- it is OK to
use vowels and words > 8 chars. People wh come around after words will
actually be able to understand things then!
Alex Papadimoulis
http://weblogs.asp.net/Alex_Papadimoulis
"MS User" wrote:

> SQL 2K
> I got a table 'DptAct' having 3 million rows with columns - ID, DPTID, NAME,
> SUPVR, MOD_DATE
> I want to retain just one row for unique (ID, DPTID) combination, doesn't
> matter with values of rest of the columns.
> Thanks In Advance
> John B
>
>
>
|||Hi
If you have a primary key on the original table then you could insert only
the rows that do not exist from your other databases. If your databases are
on the same server you can use three part names or if you create a linked
server you can use four part naming to access the second table.
Using three part names for another database (Otherdb) on the same server:
INSERT INTO DptAct ( id, deptid, col1, col2 )
SELECT id, deptid, col1, col2 FROM Otherdb..DptAct O
WHERE NOT EXISTS ( SELECT * FROM DptAct D WHERE O.od = D.Id and O.DEPTID =
d.DeptId )
col1 and col2 are example columns and should be replaced by the actual
column names.
John
"MS User" wrote:

> In my case , mod_date is not unique.
> Let me be more clear, actually I copied table 'DptAct' from different
> databases and trying to make a master 'DptAct' with Primary key (ID, DPTID).
> Here are the steps I did
> 1> Created a table DptAct_All with all columns and WITHOUT PrimaryKey
> 2> Copied DptAct into 'DptAct_All' from all databases
> 3> Now I need to get data for all the unique (ID, DPTID)
> Hope I am clear
> Thanks for your time
> John B
>
>
> "John Bell" <jbellnewsposts@.h0tmail.com> wrote in message
> news:053E1D63-B76C-4E16-A0C7-66EFC55E95C7@.microsoft.com...
>
>

Delete Duplicate

SQL 2K
I got a table 'DptAct' having 3 million rows with columns - ID, DPTID, NAME,
SUPVR, MOD_DATE
I want to retain just one row for unique (ID, DPTID) combination, doesn't
matter with values of rest of the columns.
Thanks In Advance
John BHi
Maybe if mod_date is unique for each id,dptid then
DELETE FROM DptAct
WHERE EXISTS ( SELECT * FROM DptAct d where d.id = DptAct.id and
DptAct.DPTID = d.DPTID and DptAct.Mod_Date > d.Mod_date)
John
"MS User" wrote:

> SQL 2K
> I got a table 'DptAct' having 3 million rows with columns - ID, DPTID, NAM
E,
> SUPVR, MOD_DATE
> I want to retain just one row for unique (ID, DPTID) combination, doesn't
> matter with values of rest of the columns.
> Thanks In Advance
> John B
>
>
>|||What does the DDL (i.e. Create Table statement) look like? What is the prima
ry
key on the table?
Thomas
"MS User" <sqlman@.sql.com> wrote in message
news:%23l$$uYhYFHA.3220@.TK2MSFTNGP14.phx.gbl...
> SQL 2K
> I got a table 'DptAct' having 3 million rows with columns - ID, DPTID, NAM
E,
> SUPVR, MOD_DATE
> I want to retain just one row for unique (ID, DPTID) combination, doesn't
> matter with values of rest of the columns.
> Thanks In Advance
> John B
>
>
>|||One of the ways:
http://support.microsoft.com/defaul...kb;en-us;139444
Regards.
"MS User" wrote:

> SQL 2K
> I got a table 'DptAct' having 3 million rows with columns - ID, DPTID, NAM
E,
> SUPVR, MOD_DATE
> I want to retain just one row for unique (ID, DPTID) combination, doesn't
> matter with values of rest of the columns.
> Thanks In Advance
> John B
>
>
>|||INF: How to Remove Duplicate Rows From a Table
http://support.microsoft.com/defaul...444&Product=sql
AMB
"MS User" wrote:

> SQL 2K
> I got a table 'DptAct' having 3 million rows with columns - ID, DPTID, NAM
E,
> SUPVR, MOD_DATE
> I want to retain just one row for unique (ID, DPTID) combination, doesn't
> matter with values of rest of the columns.
> Thanks In Advance
> John B
>
>
>|||In my case , mod_date is not unique.
Let me be more clear, actually I copied table 'DptAct' from different
databases and trying to make a master 'DptAct' with Primary key (ID, DPTID).
Here are the steps I did
1> Created a table DptAct_All with all columns and WITHOUT PrimaryKey
2> Copied DptAct into 'DptAct_All' from all databases
3> Now I need to get data for all the unique (ID, DPTID)
Hope I am clear
Thanks for your time
John B
"John Bell" <jbellnewsposts@.h0tmail.com> wrote in message
news:053E1D63-B76C-4E16-A0C7-66EFC55E95C7@.microsoft.com...[vbcol=seagreen]
> Hi
> Maybe if mod_date is unique for each id,dptid then
> DELETE FROM DptAct
> WHERE EXISTS ( SELECT * FROM DptAct d where d.id = DptAct.id and
> DptAct.DPTID = d.DPTID and DptAct.Mod_Date > d.Mod_date)
> John
> "MS User" wrote:
>|||John,
Hopefully you now understand WHY it's such a stupid idea to have an "ID" and
a "DEPTID" column. If DEPTID uniquley identifies the row, then MAKE IT THE
PRIMARY KEY!
Also, in SQL Server, we do not have tight naming limitations -- it is OK to
use vowels and words > 8 chars. People wh come around after words will
actually be able to understand things then!
Alex Papadimoulis
http://weblogs.asp.net/Alex_Papadimoulis
"MS User" wrote:

> SQL 2K
> I got a table 'DptAct' having 3 million rows with columns - ID, DPTID, NAM
E,
> SUPVR, MOD_DATE
> I want to retain just one row for unique (ID, DPTID) combination, doesn't
> matter with values of rest of the columns.
> Thanks In Advance
> John B
>
>
>|||Hi
If you have a primary key on the original table then you could insert only
the rows that do not exist from your other databases. If your databases are
on the same server you can use three part names or if you create a linked
server you can use four part naming to access the second table.
Using three part names for another database (Otherdb) on the same server:
INSERT INTO DptAct ( id, deptid, col1, col2 )
SELECT id, deptid, col1, col2 FROM Otherdb..DptAct O
WHERE NOT EXISTS ( SELECT * FROM DptAct D WHERE O.od = D.Id and O.DEPTID =
d.DeptId )
col1 and col2 are example columns and should be replaced by the actual
column names.
John
"MS User" wrote:

> In my case , mod_date is not unique.
> Let me be more clear, actually I copied table 'DptAct' from different
> databases and trying to make a master 'DptAct' with Primary key (ID, DPTID
).
> Here are the steps I did
> 1> Created a table DptAct_All with all columns and WITHOUT PrimaryKey
> 2> Copied DptAct into 'DptAct_All' from all databases
> 3> Now I need to get data for all the unique (ID, DPTID)
> Hope I am clear
> Thanks for your time
> John B
>
>
> "John Bell" <jbellnewsposts@.h0tmail.com> wrote in message
> news:053E1D63-B76C-4E16-A0C7-66EFC55E95C7@.microsoft.com...
>
>

Delete

Can somebody help me with a query.
carLogin is the id of the trader.
carReference is unique for the trader
Exemple:
data in my database
099 - A
099 - B
100 - A
100 - B
100 - C
100 - D
100 - E
100 - F
100 - G
100 - H
100 - I
108 - A
108 - B
Now i want delete for 1 trader (100) all the record who are not in the list
below.
ok(0)="A"
ok(1)="B"
c must be deleted
ok(2)="D"
ok(3)="E"
ok(4)="F"
g must be deleted
h must be deleted
ok(5)="I"
With other words, all the references in the list ok() must be in the
database, all the other be deleted.
Can i do this i one query.
Thx. Grard.Hi
Can you post DDL+ sample data + expected result?
"Grard Leclercq" <gerard.leclercq@.pas-de-mail.fr> wrote in message
news:m_q5f.26913$ny5.1137796@.phobos.telenet-ops.be...
> Can somebody help me with a query.
> carLogin is the id of the trader.
> carReference is unique for the trader
> Exemple:
> data in my database
> 099 - A
> 099 - B
> 100 - A
> 100 - B
> 100 - C
> 100 - D
> 100 - E
> 100 - F
> 100 - G
> 100 - H
> 100 - I
> 108 - A
> 108 - B
>
> Now i want delete for 1 trader (100) all the record who are not in the
> list
> below.
> ok(0)="A"
> ok(1)="B"
> c must be deleted
> ok(2)="D"
> ok(3)="E"
> ok(4)="F"
> g must be deleted
> h must be deleted
> ok(5)="I"
> With other words, all the references in the list ok() must be in the
> database, all the other be deleted.
> Can i do this i one query.
> Thx. Grard.
>
>
>
>
>sql

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)