Trying to build an update query

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)

Trying to build an update query

Author

Topic

rsteph
Starting Member

3 Posts

Posted - 2008-04-22 : 09:10:47

I've got a table that stores individuals, and their organization all in one table, with basic information about each (contactId, name, primary address, phone, etc.). There is also, for each line, a true/false field 'PerOrg' that lets me know if it's a person (true), or an organization (false); and a 'OrgKeyCode' field that has the contactId for the person organization.

I'm looking to make an update query that will make the primary address for all individuals set to the primary address for their corresponding organization. Here's my queries to find the items:

This gets all of the organization, their contactId and their primary Address (as long as they have one).

SELECT ContactID, FullName, PrimaryAddressType
FROM tblContactInformation
WHERE PrimaryAddressType IS NOT NULL AND PerOrg = '0'

This gets a list of all of the individuals that have an organization assigned.

SELECT ContactID, FullName, OrgKeyContactID, PrimaryAddressType
FROM tblContactInformation
WHERE PerOrg = '1' AND OrgKeyContactID IS NOT NULL

Now I need to create an update query that sets the Primary Address for individuals to the primary address for their corresponding organization. Here's what I've got worked out so far: (just the basics).

UPDATE tblContactInformation
SET PrimaryAddressType =
WHERE PerOrg = '1'

At issue is, I'm not sure how to pull the primary address only from the organization into the SET line. Do I need to save my first query, then somehow call it in my update query, where the contactID = the OrgKeyCode? Or something like that?

PeterNeo
Constraint Violating Yak Guru

357 Posts

Posted - 2008-04-22 : 09:56:35

try this,

Update CI
Set PrimaryAddressType = P.PrimaryAddressType
From tblContactInformation CI
Inner Join (Select ContactID, FullName, PrimaryAddressType
From tblContactInformation
Where PrimaryAddressType IS NOT NULL AND PerOrg = '0') P On P.ContactID = CI.OrgKeyContactID
Where CI.PerOrg = '1' AND CI.OrgKeyContactID IS NOT NULL

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources