Delete Duplicate

26 May 2005 5:13 PM

MS User

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

26 May 2005 5:28 PM

John Bell

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

Show quoteHide quote

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

26 May 2005 6:30 PM

MS User

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

Show quoteHide quote

"John Bell" <jbellnewspo***@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:
>
>> 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
>>
>>
>>
>>
>>
>>

27 May 2005 6:30 AM

John Bell

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

Show quoteHide quote

"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" <jbellnewspo***@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:
> >
> >> 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
> >>
> >>
> >>
> >>
> >>
> >>
>
>
>

26 May 2005 5:28 PM

Thomas Coleman

What does the DDL (i.e. Create Table statement) look like? What is the primary
key on the table?

Thomas

Show quoteHide quote

"MS User" <sql***@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
>
>
>
>
>

26 May 2005 5:31 PM

SkyWalker

One of the ways:
http://support.microsoft.com/default.aspx?scid=kb;en-us;139444

Regards.

Show quoteHide quote

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

26 May 2005 6:01 PM

Alejandro Mesa

INF: How to Remove Duplicate Rows From a Table
http://support.microsoft.com/default.aspx?scid=kb;en-us;139444&Product=sql

AMB

Show quoteHide quote

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

26 May 2005 9:10 PM

Alex Papadimoulis

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

Show quoteHide quote

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