Need to remove dups. Here is an example of the db

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Need to remove dups. Here is an example of the db

Author

Topic

gotafly
Yak Posting Veteran

54 Posts

Posted - 2007-12-21 : 13:41:20

150, 07005025, RICHARD L IRREVOCABLE & TRUST PIATT, 318 EMILY LN, NEW CUMBERLAND, PA, 17070
151, 07008042, DEVELOPERS INC PROJECT, 240 S 13TH ST, HARRISBURG, PA, 17104
152, 07008046, HOWARD L JR LEWIS, 1510 HERR ST, HARRISBURG PA, 17103
153, 07009017, DEVELOPERS INC PROJECT, 240 S 13TH ST, HARRISBURG PA, 17104
154, 07012001, DEVELOPERS INC PROJECT, 240 S 13TH ST, HARRISBURG PA, 17104
155, 07012002, DEVELOPERS INCORPORATE PROJECT, 240 S 13TH ST, HARRISBURG, PA, 17104

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2007-12-21 : 13:50:09

Can you give some details about table structure and your expected data?

gotafly
Yak Posting Veteran

54 Posts

Posted - 2007-12-21 : 14:08:24

I just want to remove the dups on the duplicate addresses

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2007-12-21 : 14:09:52

You still have to show us the table structure.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

gotafly
Yak Posting Veteran

54 Posts

Posted - 2007-12-21 : 14:30:40

The first is indexed. The rest are varchar

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2007-12-21 : 14:33:19

I guess we can't help you then since you are unwilling to show us the table structure.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2007-12-21 : 14:34:59

Sorry we want more info on the table. Basically the fields of table specifying pk column and also what you expect to be the final data in table.

gotafly
Yak Posting Veteran

54 Posts

Posted - 2007-12-21 : 14:38:35

ID int Unchecked
Field1 nvarchar(255) Checked
Field2 nvarchar(255) Checked
Field3 nvarchar(255) Checked
Field4 nvarchar(255) Checked
Field5 nvarchar(255) Checked
Field6 nvarchar(255) Checked
Field7 nvarchar(255) Checked

This it what should be left
150, 07005025, RICHARD L IRREVOCABLE & TRUST PIATT, 318 EMILY LN, NEW CUMBERLAND, PA, 17070
151, 07008042, DEVELOPERS INC PROJECT, 240 S 13TH ST, HARRISBURG, PA, 17104
152, 07008046, HOWARD L JR LEWIS, 1510 HERR ST, HARRISBURG PA, 17103
153, 07009017, DEVELOPERS INC PROJECT, 240 S 13TH ST, HARRISBURG PA, 17104 ***********Delete
154, 07012001, DEVELOPERS INC PROJECT, 240 S 13TH ST, HARRISBURG PA, 17104 ***********Delete
155, 07012002, DEVELOPERS INCORPORATE PROJECT, 240 S 13TH ST, HARRISBURG, PA, 17104

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2007-12-21 : 14:46:25

[code]DELETE t1.* FROM
Table t1
INNER JOIN
(
SELECT ROW_NUMBER() OVER (PARTITION BY Field3,Field4,Field5,Field6,Field7 ORDER BY ID) AS 'RowNo',
ID ,
Field1 ,
Field2 ,
Field3 ,
Field4,
Field5 ,
Field6 ,
Field7
FROM Table
)t2
ON t1.ID=t2ID
AND t2.RowNo<>1[/code]

gotafly
Yak Posting Veteran

54 Posts

Posted - 2007-12-24 : 06:09:11

I am getting the following errors
Msg 102, Level 15, State 1, Line 1
Incorrect syntax near '*'.
Msg 102, Level 15, State 1, Line 5
Incorrect syntax near 't2'.

pootle_flump

1064 Posts

Posted - 2007-12-24 : 06:45:43

Lord knows how many methods available here:
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=6256

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-12-24 : 07:21:42

quote:

Originally posted by visakh16

DELETE t1.* FROM
Table t1
INNER JOIN
(
SELECT ROW_NUMBER() OVER (PARTITION BY Field3,Field4,Field5,Field6,Field7 ORDER BY ID) AS 'RowNo',
ID ,
Field1 ,
Field2 ,
Field3 ,
Field4,
Field5 ,
Field6 ,
Field7 
FROM Table
)t2
ON t1.ID=t2ID
AND t2.RowNo<>1

You dont need to specify columns in DELETE statement

Madhivanan

Failing to plan is Planning to fail

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2007-12-24 : 09:43:59

quote:
Originally posted by madhivanan

quote:
Originally posted by visakh16

DELETE t1.* FROM
Table t1
INNER JOIN
(
SELECT ROW_NUMBER() OVER (PARTITION BY Field3,Field4,Field5,Field6,Field7 ORDER BY ID) AS 'RowNo',
ID ,
Field1 ,
Field2 ,
Field3 ,
Field4,
Field5 ,
Field6 ,
Field7
FROM Table
)t2
ON t1.ID=t2ID
AND t2.RowNo<>1

You dont need to specify columns in DELETE statement

Madhivanan

Failing to plan is Planning to fail

Thats the result of copy pasting from SELECT

gotafly
Yak Posting Veteran

54 Posts

Posted - 2007-12-24 : 09:48:28

Sorry I do not follow

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-12-24 : 09:52:52

quote:
Originally posted by gotafly

Sorry I do not follow

In visakh16's example, use

DELETE FROM
Table t1
INNER JOIN
(
SELECT ROW_NUMBER() OVER (PARTITION BY Field3,Field4,Field5,Field6,Field7 ORDER BY ID) AS 'RowNo',
ID ,
Field1 ,
Field2 ,
Field3 ,
Field4,
Field5 ,
Field6 ,
Field7
FROM Table
)t2
ON t1.ID=t2ID
AND t2.RowNo<>1

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources