Updating a column with a foreign key

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)

Updating a column with a foreign key

Author

Topic

josh14
Starting Member

4 Posts

Posted - 2010-02-14 : 14:39:38

Hi,

My question is this:

I have 2 tables one is a contacts table with a column of organizations names, the other table is an organizations table with 2 columns: ID, organization name.

I need to replace contacts table organizations names with the matching foreign key of the organizations table.

The 2 columns match I just don't know how to update all the records in a single shot. Here is the select inner join


select o.id
from temp c 
inner join organizations o on  c.organizationID = o.organization_name

Thanks for the help!

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-02-14 : 14:45:49

update c
set organizationID = o.ID
from contacts c
join organizations o
on o.organization_name = c.organization_name

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

josh14
Starting Member

4 Posts

Posted - 2010-02-14 : 14:49:52

ah I a getting an error by "From", code this be that its a MySQL DB? I usually use SQL Server

josh14
Starting Member

4 Posts

Posted - 2010-02-14 : 14:56:13

it seemed this was the trick


update temp c, organizations o
set organizationID = o.ID
where o.organization_name = c.organizationID

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-02-14 : 14:57:35

The code I provided is for SQL Server.
I don't know what kind of server you are using (MySQL or SQL Server).

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

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2010-02-14 : 15:14:56

oops. you already found the solution.

I think this should work in MYSQL

Update contacts,Organizations  
Set contacts.OrganizationId = Organizations.Id
Where contacts.Organization_name = Organizations.Organization_name

josh14
Starting Member

4 Posts

Posted - 2010-02-14 : 15:21:23

Thanks webfred and sodeep for you help!!!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources