Update duplicates ID - SQL Server Forums

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)

Update duplicates ID

Author

Topic

infodemers
Posting Yak Master

183 Posts

Posted - 2009-10-28 : 08:25:03

Hi,

I have a table with 3 coulumns. I have duplicates ID I wish to update
with the top 1 ID plus one.

ID Region Customer
0100002296 ON GIFFELS DESIGN-BUILD INC
0100002296 QC GESTION MD
0100002686 QC CENT HOPITAL LUCILLE TEASDALE
0100002686 ON CANADIAN OPERA HOUSE CORPORATION

Thanks for any help on this!

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-10-28 : 08:42:07

See if this gives you expected result

select Id-sno,Region,Customer from
(
select ID, Region, Customer, row_number() over(partition by ID order by Id) as sno from your_table
) as t

Madhivanan

Failing to plan is Planning to fail

infodemers
Posting Yak Master

183 Posts

Posted - 2009-10-28 : 09:00:19

Hi Madhivanan,

That is almost it. Instead of increasing the existing duplicates ID it decreases them. So when I have 0100002296 , it becomes
0100002295 and 0100002294.

InfoDemers

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-10-28 : 09:03:14

Try this

select Id+sno-1,Region,Customer from
(
select ID, Region, Customer, row_number() over(partition by ID order by Id) as sno from your_table
) as t

Madhivanan

Failing to plan is Planning to fail

infodemers
Posting Yak Master

183 Posts

Posted - 2009-10-28 : 09:25:23

Hi Madhivanan,

Doing it that way, it gives me an ID that already exist as a unique row.Because in my table, I do also have unique row, not just duplicates.
I tried the following one, but it does not work. It still gives me duplicates.

select ID+sno +(select top 1 ID from my_table), Region, CustomerName from
(select ID, Region, CustomerName, row_number() over(partition by ID order by ID) as sno from my_table
) as t
order by ID

Thanks!

InfoDemers

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-10-28 : 10:04:11

Ok. What do you want to have for unique ids?

Madhivanan

Failing to plan is Planning to fail

infodemers
Posting Yak Master

183 Posts

Posted - 2009-10-28 : 10:24:52

Hi Madhivanan,

I wish to get the next available ID from the table.
This is what I meant to say by writing (select top 1 ID from my_table order by ID desc). Like the second duplicated ID should have as a new ID, the next available ID number. Let's say 0100002296 QC Gestion MD becomes 0200009422 QC Gestion MD. The first duplicated ID should remain the same.

ID Region Customer
0100002296 ON GIFFELS DESIGN-BUILD INC
0100002296 QC GESTION MD
0100002686 QC CENT HOPITAL LUCILLE TEASDALE
0100002686 ON CANADIAN OPERA HOUSE CORPORATION
0200009421 ON Beaver Bell <---- This is the last row.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-10-29 : 02:22:40

quote:
Originally posted by infodemers

Hi Madhivanan,

I wish to get the next available ID from the table.
This is what I meant to say by writing (select top 1 ID from my_table order by ID desc). Like the second duplicated ID should have as a new ID, the next available ID number. Let's say 0100002296 QC Gestion MD becomes 0200009422 QC Gestion MD. The first duplicated ID should remain the same.

ID Region Customer
0100002296 ON GIFFELS DESIGN-BUILD INC
0100002296 QC GESTION MD
0100002686 QC CENT HOPITAL LUCILLE TEASDALE
0100002686 ON CANADIAN OPERA HOUSE CORPORATION
0200009421 ON Beaver Bell <---- This is the last row.

Post your expected result for these inputs

Madhivanan

Failing to plan is Planning to fail

matrixmind
Starting Member

9 Posts

Posted - 2009-10-29 : 03:07:59

quote:
Originally posted by infodemers

Hi,

I have a table with 3 coulumns. I have duplicates ID I wish to update
with the top 1 ID plus one.

ID Region Customer
0100002296 ON GIFFELS DESIGN-BUILD INC
0100002296 QC GESTION MD
0100002686 QC CENT HOPITAL LUCILLE TEASDALE
0100002686 ON CANADIAN OPERA HOUSE CORPORATION

Thanks for any help on this!

Hi infodemers

try this Query i think this Query Solve Your Problem
With abc
as
(
select ID,Region,Customer,Row_Number() Over(Order by ID) as [Row] From HK
)

update HK set ID=(Select TOP 1 ID From HK)+(select Row From abc where ID=HK.ID and abc.Region=HK.Region)

Dinesh Sharma
Matrix Solution
Sr.Software Engg.

infodemers
Posting Yak Master

183 Posts

Posted - 2009-10-29 : 09:00:33

Hi matrixmind,

I like your solution but when I try it, I get an Invalid object name 'abc'...
Do you have any idea?

Thanks!
InfoDemers

infodemers
Posting Yak Master

183 Posts

Posted - 2009-10-29 : 09:28:19

Hi matrixmind,

I modified your solution a bit and now I get an error with the + sign.

Select * into #temp2 from
(Select ID,Region,Customer,Row_Number() Over(Order by ID) as [Row] From HK)
update HK set ID=(Select TOP 1 ID From HK)+(select Row From #temp2 where ID=HK.ID and #temp2.Region=HK.Region)

Thanks again!
InfoDemers

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources