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

Posted on 2007-12-05
Last Modified: 2010-03-20
Hi there I have 2 tables.




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

Is there anyway of updating this field based on the ID column of tblLocation even though it is now a varchar field?
Question by:MayoorPatel
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
LVL 16

Expert Comment

ID: 20412142
UPDATE tblProjects
SET p.LocationName = l.LocationName
FROM tblProjects p INNER JOIN tblLocation l on =
LVL 15

Expert Comment

ID: 20412159
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>)


Author Comment

ID: 20412462
Ok I need to update thje column LocationId in

with the location name field from this table
LVL 15

Accepted Solution

JimFive earned 500 total points
ID: 20412980
So you want
Update tblProjects
Set LocationID = (Select LocationName FRom tblLocation where = tblLocation.locationid)

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

by Mark Wills Attending one of Rob Farley's seminars the other day, I heard the phrase "The Accidental DBA" and fell in love with it. It got me thinking about the plight of the newcomer to SQL Server...  So if you are the accidental DBA, or, simp…
If you find yourself in this situation “I have used SELECT DISTINCT but I’m getting duplicates” then I'm sorry to say you are using the wrong SQL technique as it only does one thing which is: produces whole rows that are unique. If the results you a…
Michael from AdRem Software outlines event notifications and Automatic Corrective Actions in network monitoring. Automatic Corrective Actions are scripts, which can automatically run upon discovery of a certain undesirable condition in your network.…
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…

624 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question