Solved

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

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

Featured Post

Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

Join & Write a Comment

Suggested Solutions

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…
This video discusses moving either the default database or any database to a new volume.
This tutorial demonstrates a quick way of adding group price to multiple Magento products.

758 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

Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!

Get 1:1 Help Now