update to same table - 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

General SQL Server Forums

New to SQL Server Programming

update to same table

Author

Topic

vijays3
Constraint Violating Yak Guru

354 Posts

Posted - 2010-09-11 : 10:54:24

Hi All,

I have one account table in which i have multiple records for single account and my requirement is to update the all records with the the account's record with the most complete valid address information
I have following records for example

AccountNumber AccountName Address 1 City St Postal Phone
11861 Merck and Company, Inc. 126 E. Lincoln Avenue Rahway NJ 07065 732/594-6014
11861 Merck and Company, Inc. 126 E. Lincoln Avenue Rahway NJ 07065
15185 Thomas & Betts Corporation 32425 Aurora Road Memphis TN 38125
15185 Thomas & Betts Corporation 32425 Aurora Road Memphis TN 38125 440/914-8862
16304 HanesBrands Inc. 1000 E. Hanes Mill Rd. Winston Salem NC 27105 336 5195697
16304 HanesBrands Inc. 1000 E. Hanes Mill Rd. Winston Salem NC 27105
14532 Novartis Grimsby Limited Pyewipe Grimsby LINCS DN31 2SR 441472253590
14532 Novartis Grimsby Limited Pyewipe Grimsby LINCS DN31 2SR

I need to update the all records of particular accountnumber with the most complete address information e.g.
account number 11861
all records should be upadted with the
Merck and Company, Inc. 126 E. Lincoln Avenue Rahway NJ 07065 732/594-6014
because in second record phone is missing for this account..

Same logic i have to implement for all other accounts.

slimt_slimt
Aged Yak Warrior

746 Posts

Posted - 2010-09-12 : 03:20:24

this will help you:


create table AA
(id int
,name varchar(10)
,phone varchar(10)
)

insert AA
select 10,'lala',''
union all select 10, 'lala','1212'
union all select 11, 'lili','123'
union all select 12,'lll',''
union all select 12,'lll','1234567'


update A
set a.phone = a1.phone
from AA  as a
join AA as a1
on a.id = a1.id
and a.name = a1.name
where 
	len(a1.phone)>1
	and len(a.phone)=0

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-09-12 : 06:49:41

You should not look to replenish the data.
You should look to get rid of the duplicate records!

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

slimt_slimt
Aged Yak Warrior

746 Posts

Posted - 2010-09-12 : 12:50:11

That's true :) data should be normalized :) and deduplicated :)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources