Update query - 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 query

Author

Topic

icw
Constraint Violating Yak Guru

378 Posts

Posted - 2010-08-03 : 01:40:52

I Know the answer is going to be fairly straight forward and I'm going to realise i could have figured it out myself but I've been working all night and ran out of coffee a few hours ago. Can anyone help?

I have parent records (company) and child records (Contacts) within the same table. Naturally its common to have more than one contact per company.
The company records have the Company field populated and have a '1' in a field called level
The contact fields do not have the company field populated and have a '2' in the field called level.
All records in the same organisation share a unique code in a field called key5

My objective is that i want to have the contact records populated with the correct company name.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-08-03 : 01:44:28

so what all values will you input for insertion?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

icw
Constraint Violating Yak Guru

378 Posts

Posted - 2010-08-03 : 02:14:33

Hi
I want to give populate the company field for contacts (level2 records)
Here is an example of data

row Company Contact level key5
1 "ABC Imports" "" "1" "3456"
2 "" "Peter finch" "2" "3456"
3 "" "Sara Brown" "2" "3456"
4 "WEP Systems" "" "1" "7899"
5 "" "Mike Hall" "2" "7899"
6 "" "Bud Smith" "2" "7899"

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-08-03 : 02:25:38

ok then you can do update like


UPDATE t
SET t.Company=t1.Company
FROM Table t
INNER JOIN Table t1
ON t1.key5 = t.key5
AND t1.Company IS NOT NULL
AND t.Company IS NULL

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

icw
Constraint Violating Yak Guru

378 Posts

Posted - 2010-08-03 : 03:02:32

Thats great thanks
I'll try it this morning.
Do I not need to reference the Level field at all?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-08-03 : 03:03:32

no need provided one set does not have company information and other set does have it

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources