how to update duplicate record

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

SQL Server Administration (2005)

how to update duplicate record

Author

Topic

AdiI
Starting Member

31 Posts

Posted - 2009-09-15 : 05:32:13

i have 2 duplicate record in table both are same now i want to update one record and keep one as it is. how can i update in sql server. i tried to find solution on internet. i didn't find SQL server solution i found DB2 RRN function. it can solve my problem but it is not supported in sql server if u want to check what i want to do plz visit flowing link plz suggest any solution in sql server

http://www.itjungle.com/fhg/fhg120507-story02.html

adeel

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2009-09-15 : 05:40:09

In this forum you can find many many examples to solve this using derived table and row_number().

No, you're never too old to Yak'n'Roll if you're too young to die.

AdiI
Starting Member

31 Posts

Posted - 2009-09-15 : 06:09:12

plz explain this point i did not get your point

adeel

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2009-09-15 : 06:18:14

[code]
declare @sample table(Name varchar(255),Age int,Location varchar(255))
insert @sample
select 'Bill Fold', 18, 'Moscow' union all
select 'Sue Doe-Nymm', 25, 'Athens' union all
select 'Ben Dover', 22, 'Helsinki' union all
select 'Bill Fold', 18, 'Moscow' union all
select 'Polly Wogg', 19, 'Helsinki'

select * from @sample

update dt
set Name = 'Sam O''Var'
from
(
select
row_number() over (partition by Name,Age,Location order by Name,Age,Location) as rownum,
Name,
Age,
Location
from @sample
)dt
where rownum = 1
and Name = 'Bill Fold'

select * from @sample
[/code]

No, you're never too old to Yak'n'Roll if you're too young to die.

AdiI
Starting Member

31 Posts

Posted - 2009-09-15 : 06:24:54

INSERT INTO [dbo].[tmpLinkedTableFieldNew] ([FieldId], [FieldDataType], [FieldLength], [Caption], [Required], [DefaultValue], [Format], [InputMask], [DecimalPlaces], [NewValues], [ColumnPosition], [TokenID]) VALUES ('24a99300-75c0-430d-9a07-75c85d02d41c', 'VARCHAR', 200, 'Caller', 0, 'Key', '', '', 0, '', 1, 'e7da9cf8-6475-4b80-a65e-929ec224a25c')
INSERT INTO [dbo].[tmpLinkedTableFieldNew] ([FieldId], [FieldDataType], [FieldLength], [Caption], [Required], [DefaultValue], [Format], [InputMask], [DecimalPlaces], [NewValues], [ColumnPosition], [TokenID]) VALUES ('c536d69e-fe4e-46de-9da8-5c7d429048d5', 'NUMERIC', 18, 'Duration', 0, '', 'General Number', '', 0, '', 5, 'e7da9cf8-6475-4b80-a65e-929ec224a25c')
INSERT INTO [dbo].[tmpLinkedTableFieldNew] ([FieldId], [FieldDataType], [FieldLength], [Caption], [Required], [DefaultValue], [Format], [InputMask], [DecimalPlaces], [NewValues], [ColumnPosition], [TokenID]) VALUES ('24a99300-75c0-430d-9a07-75c85d02d41c', 'VARCHAR', 200, 'Caller', 0, 'Key', '', '', 0, '', 1, '0b2cfb6f-58a0-4d5a-88b0-161e1198b3c5')
INSERT INTO [dbo].[tmpLinkedTableFieldNew] ([FieldId], [FieldDataType], [FieldLength], [Caption], [Required], [DefaultValue], [Format], [InputMask], [DecimalPlaces], [NewValues], [ColumnPosition], [TokenID]) VALUES ('c536d69e-fe4e-46de-9da8-5c7d429048d5', 'NUMERIC', 18, 'Duration', 0, '', 'General Number', '', 0, '', 5, '0b2cfb6f-58a0-4d5a-88b0-161e1198b3c5')
INSERT INTO [dbo].[tmpLinkedTableFieldNew] ([FieldId], [FieldDataType], [FieldLength], [Caption], [Required], [DefaultValue], [Format], [InputMask], [DecimalPlaces], [NewValues], [ColumnPosition], [TokenID]) VALUES ('4ff52477-575a-4ba7-8f88-077923493ef5', 'DECIMAL', 18, 'id', 0, 'Key', 'General Number', '', 2, '', 1, 'b98170ad-9aab-4294-9671-60838a7658ed')
INSERT INTO [dbo].[tmpLinkedTableFieldNew] ([FieldId], [FieldDataType], [FieldLength], [Caption], [Required], [DefaultValue], [Format], [InputMask], [DecimalPlaces], [NewValues], [ColumnPosition], [TokenID]) VALUES ('9341eac2-0faa-4824-9d1d-9889a2212e62', 'DECIMAL', 18, 'amount', 0, '', 'General Number', '', 2, '', 3, 'b98170ad-9aab-4294-9671-60838a7658ed')
INSERT INTO [dbo].[tmpLinkedTableFieldNew] ([FieldId], [FieldDataType], [FieldLength], [Caption], [Required], [DefaultValue], [Format], [InputMask], [DecimalPlaces], [NewValues], [ColumnPosition], [TokenID]) VALUES ('e0132211-d387-4d6e-956b-c434e1c28261', 'VARCHAR', 200, 'name', 0, '', '', '', 0, '', 2, 'b98170ad-9aab-4294-9671-60838a7658ed')

now i just want to replace one duplicate id with new id how can i do plz help

adeel

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2009-09-15 : 06:40:06

In your sample date from your last post I see you can retrieve the wanted record by using FielId in combination with TokenID.

No, you're never too old to Yak'n'Roll if you're too young to die.

AdiI
Starting Member

31 Posts

Posted - 2009-09-15 : 06:42:53

update dt
set FieldId = '5EC311AE-1CEB-4281-A0B9-91F29BD7B0CB'
from
(
select
row_number() over (partition by FieldId,FieldDataType, FieldLength, Caption,Required ,DefaultValue ,Format, InputMask, DecimalPlaces, NewValues, ColumnPosition,TokenID
order by FieldId,FieldDataType, FieldLength, Caption,Required ,DefaultValue ,Format, InputMask, DecimalPlaces, NewValues, ColumnPosition,TokenID ) as rownum,
FieldId,FieldDataType, FieldLength, Caption,Required ,DefaultValue ,Format, InputMask, DecimalPlaces, NewValues, ColumnPosition,TokenID from [tmp1LinkedTableFieldNew]
)dt
where rownum = 1
and FieldId = '24a99300-75c0-430d-9a07-75c85d02d41c'

i have run this but this update two row. i want to update one

adeel

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2009-09-15 : 06:47:15

Did you see my last post?
It is the conclusion.
row_number() gives always 1 because there are no distinct rows.
You can use a simple update like I told in my last post.

No, you're never too old to Yak'n'Roll if you're too young to die.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources