update foreign key field with proper value

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 foreign key field with proper value

Author

Topic

drpcken
Starting Member

20 Posts

Posted - 2009-01-09 : 23:13:40

I'm working on updating a database thats been poorly managed (use to be access) to prepare it for a web application I wrote.

There is a Contact table with a foreign key column called BrokerTypeID. It is suppose to be the primary key for the BrokerType table, however due to poor management the actual BrokerTypeName was inserted instead of the BrokerTypeID for the contacts table. I need this updated but I'm not sure how without writing an update statement for each brokertype.

Something like this:

UPDATE contacts SET brokertypeid = (select brokertypeid from brokertypes where brokertypes.brokertypename = contacts.brokertypeid);

I hope this makes sense. I need to replace the BrokerTypeName value in the Contacts table with the corresponding BrokerTypeID value in the BrokerTypes table.

Nageswar9
Aged Yak Warrior

600 Posts

Posted - 2009-01-09 : 23:52:33

UPDATE contacts
SET brokertypeid = (select brokertypeid from brokertypes b inner join contacts c on b.brokertypeid = c.brokertypeid ),
brokertypename =( select brokertypename from brokertypes b inner join contacts c on b.brokertypeid = c.brokertypeid )
where brokertypes.brokertypeid = contacts.brokertypeid

raky
Aged Yak Warrior

767 Posts

Posted - 2009-01-09 : 23:55:40

try this

UPDATE c
SET c.brokertypeid = b.brokertypeid
from brokertypes b
inner join contacts c
where b.brokertypename = c.brokertypename

drpcken
Starting Member

20 Posts

Posted - 2009-01-10 : 00:05:05

Sorry!! I meant to say this is STILL in access. I'm getting it ready for sql 2005... I tried both queries but tehy didn't work.

raky, your query errors and says:
syntax error (missing operator) in query expression b.brokertypeid from brokertypes b inner join contacts c

is that becasue its access 2007 and not sql 2005? I didn't think there was much difference in the sql statements between the two

Sorry for the confusion!

bklr
Master Smack Fu Yak Hacker

1693 Posts

Posted - 2009-01-10 : 00:07:43

quote:
Originally posted by raky

try this

UPDATE c
SET c.brokertypeid = b.brokertypeid
from brokertypes b
inner join contacts c
~~where~~ on b.brokertypename = c.brokertypename

jbp_j
Starting Member

24 Posts

Posted - 2009-01-10 : 01:03:18

How u r insert a BrokertypeId with Name in contacts table.
while Inserting the data It gives Foreign Key violation Error.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-01-10 : 02:09:53

quote:
Originally posted by drpcken

Sorry!! I meant to say this is STILL in access. I'm getting it ready for sql 2005... I tried both queries but tehy didn't work.

raky, your query errors and says:
syntax error (missing operator) in query expression b.brokertypeid from brokertypes b inner join contacts c

is that becasue its access 2007 and not sql 2005? I didn't think there was much difference in the sql statements between the two

Sorry for the confusion!

if you want access specific solution please post this in access forum

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources