asked on

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?

SQL_SERVER_DBA

UPDATE tblProjects
SET p.LocationName = l.LocationName
FROM tblProjects p INNER JOIN tblLocation l on p.id = l.id

JimFive

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

MayoorPatel

ASKER

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

ASKER CERTIFIED SOLUTION

JimFive

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial