Update field as int - 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 field as int

Author

Topic

gotafly
Yak Posting Veteran

54 Posts

Posted - 2007-12-26 : 14:13:33

I have a field in a tablea that is a varchar "FormOfAddressName". I made another table from the distinct values of the field table "tbl_FormOfAddress" In that table there is:
FormOfAddressID int
FormOfAddressName nvarchar(50)

I added a field in tablea
FormOfAddressID int

Now I need to join the 2 tables and fill that FormOfAddressID in tablea from the FormOfAddressID field in the "tbl_FormOfAddress" Table. That field is indexed. How do I do that?

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2007-12-26 : 14:24:23

SELECT *
FROM TableA a
INNER JOIN TableB b
ON a.FormOfAddressID = b.FormOfAddressID

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

gotafly
Yak Posting Veteran

54 Posts

Posted - 2007-12-26 : 14:32:31

Where is the Update?

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2007-12-26 : 14:36:06

Your post didn't say anything about an update. It does say it in the subject, but you really need to be more clear in the post.

How can we determine which row in TableA to update? Does the FormOfAddressName column exist in that table too?

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

gotafly
Yak Posting Veteran

54 Posts

Posted - 2007-12-26 : 14:38:55

Yes Sorry

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2007-12-26 : 14:41:17

UPDATE a
SET FormOfAddressID = b.FormOfAddressID
FROM TableA a
INNER JOIN tbl_FormOfAddress b
ON a.FormOfAddressName = b.FormOfAddressName

You should remove the FormOfAddressName column from the child table once the UPDATE has been run.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

gotafly
Yak Posting Veteran

54 Posts

Posted - 2007-12-26 : 14:45:38

Thanks Tara

gotafly
Yak Posting Veteran

54 Posts

Posted - 2007-12-26 : 15:02:14

Does not seem to work. I get
Column or expression 'FormOfAddressID' cannot be updated.

UPDATE a
SET a.FormOfAddressID = b.FormOfAddressID
FROM tbl_Name a
INNER JOIN tbl_FormOfAddress b
ON a.FormOfAddressName = b.FormOfAddressName

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2007-12-26 : 15:04:06

You didn't copy my query correctly.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

gotafly
Yak Posting Veteran

54 Posts

Posted - 2007-12-26 : 15:07:51

TableA is tbl_Name

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2007-12-26 : 15:11:06

That's not the part I'm referring to. Compare my posted query to what you've tried.

You've added two characters that shouldn't be there.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

gotafly
Yak Posting Veteran

54 Posts

Posted - 2007-12-26 : 15:15:33

I took off the a. It still does not work

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2007-12-26 : 15:19:53

In what tool are you running the query?

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

gotafly
Yak Posting Veteran

54 Posts

Posted - 2007-12-26 : 15:23:55

I ran a new query in the Management studio

gotafly
Yak Posting Veteran

54 Posts

Posted - 2007-12-26 : 15:28:39

Had to change the this:
ON FormOfAddressName = b.FormOfAddressName

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2007-12-26 : 15:30:22

That doesn't make sense, but okay.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

gotafly
Yak Posting Veteran

54 Posts

Posted - 2007-12-26 : 15:32:46

Well That did not work, but I figured it out

UPDATE tbl_Name
SET FormOfAddressID =
(SELECT FormOfAddressID
FROM tbl_FormOfAddress
WHERE (tbl_Name.FormOfAddress = FormOfAddressName))

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-12-26 : 15:57:40

UPDATE a
SET a.FormOfAddressID = b.FormOfAddressID
FROM tbl_Name AS a
INNER JOIN tbl_FormOfAddress AS b ON b.FormOfAddressName = a.FormOfAddress

E 12°55'05.25"
N 56°04'39.16"

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2007-12-26 : 16:03:35

You don't need the a. after the SET.

My query should work fine if he changes the names of the objects to match his actual object names. I used what his initial post indicated.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources