Solved

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

Posted on 2007-12-05
4
200 Views
Last Modified: 2010-03-20
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?
0
Comment
Question by:MayoorPatel
[X]
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
4 Comments
 
LVL 16

Expert Comment

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

Expert Comment

by:JimFive
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>)

--
JimFive
0
 
LVL 1

Author Comment

by:MayoorPatel
ID: 20412462
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
 
LVL 15

Accepted Solution

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

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering 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

Data architecture is an important aspect in Software as a Service (SaaS) delivery model. This article is a study on the database of a single-tenant application that could be extended to support multiple tenants. The application is web-based develope…
'Between' is such a common word we rarely think about it but in SQL it has a very specific definition we should be aware of. While most database vendors will have their own unique phrases to describe it (see references at end) the concept in common …

751 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