SQL Server Update Fields

Glenn Stearns
Glenn Stearns used Ask the Experts™
on
Look at the attachment.

You'll see that there are multiple records for the field 'new_trackingno' .  In most of these sets of multiple records, the field 'new_accountname' is populated only one time.  I need to create a SQL script that will populate the records where the blank 'new_accountname' field is NULL with the same data that appears in the 'new_accountname' field that is populated - where 'new_trackingno' is the same.  For example, records two, three, and four in the attachment are for new_trackingno 5808, but only the third record's new_accountname field is populated.  The script would populate 'new_accountname in records two and four with the data in 'new_accountname' in the third record - where 'new_trackingno' is 5808.  

Records five through 8 are the second set - where new_trackingno is 5815, but only record six has data in new_accountname. The script would populate records five, seven, and eight with the new_accountname data in record six...and so on.

Thanks!
SQL-Table.pdf
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
leakim971Multitechnician
Top Expert 2014

Commented:
Hello glennes,

Try this one :


UPDATE thetablename, d SET thetablename.new_accountname = d.new_accountname FROM thetablename, (SELECT distinct new_accountname,new_trackingno FROM thetablename WHERE new_accountname IS NOT NULL) d WHERE d.new_trackingno = thetablename.new_trackingno

Open in new window

Multitechnician
Top Expert 2014
Commented:
corrected and tested :
replace thetablename by your table name of course :


UPDATE thetablename SET thetablename.new_accountname = d.new_accountname FROM thetablename, (SELECT distinct new_accountname,new_trackingno FROM thetablename WHERE new_accountname IS NOT NULL) d WHERE d.new_trackingno = thetablename.new_trackingno

Open in new window

Glenn StearnsAnalyst

Author

Commented:
Worked perfectly!
Thanks!

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial