Updating a Table Coumn with the values from another Table with the same Column Type

Hi there I have 2 tables.

tblLocation
id
LocationName
MapLogo

and

tblProjects
LocationName

I need to update tblProjects.LocationName with the values from tblLocation.LocationName, usually pretty straight forward, however the tblProjects.LocationName is now a varchar and has an id in it which references tblLocation.id.

Is there anyway of updating this field based on the ID column of tblLocation even though it is now a varchar field?
LVL 1
MayoorPatelAsked:
Who is Participating?
 
JimFiveCommented:
So you want
Update tblProjects
Set LocationID = (Select LocationName FRom tblLocation where tblProjects.id = tblLocation.locationid)
0
 
SQL_SERVER_DBACommented:
UPDATE tblProjects
SET p.LocationName = l.LocationName
FROM tblProjects p INNER JOIN tblLocation l on p.id = l.id
0
 
JimFiveCommented:
Yes, but I need more info.  Is there some sort of delimiter in tblProjects that indicates the id?

Generically, what you would do is something like:

update tblProjects
Set LocationName = (Select LocationName from tblLocation WHERE tblLocation.ID = SUBSTR(tblProjects.locationName,1,<wherever the ID Ends>)
WHERE EXISTS (Select * from tblLocation Where tblLocation.ID = SUBSTR(tblProjects.locationName,1,<wherever the id ends>)

--
JimFive
0
 
MayoorPatelAuthor Commented:
Ok I need to update thje column LocationId in
http://www.mayoor.co.uk/tblProjectsLocationId.jpg

with the location name field from this table
http://www.mayoor.co.uk/tbllocationLocationId.jpg
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.