Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

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

Posted on 2007-12-05
4
Medium Priority
?
210 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
  • 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 2000 total points
ID: 20412980
So you want
Update tblProjects
Set LocationID = (Select LocationName FRom tblLocation where tblProjects.id = tblLocation.locationid)
0

Featured Post

Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

Question has a verified solution.

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

If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
An alternative to the "For XML" way of pivoting and concatenating result sets into strings, and an easy introduction to "common table expressions" (CTEs). Being someone who is always looking for alternatives to "work your data", I came across this …
This Micro Tutorial will teach you how to add a cinematic look to any film or video out there. There are very few simple steps that you will follow to do so. This will be demonstrated using Adobe Premiere Pro CS6.
This course is ideal for IT System Administrators working with VMware vSphere and its associated products in their company infrastructure. This course teaches you how to install and maintain this virtualization technology to store data, prevent vuln…

972 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