Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Updating one table with values from another

Posted on 2008-11-15
4
Medium Priority
?
204 Views
Last Modified: 2012-05-05
I have the following sql statement and I get a "cannot be bound" error.

update Products
set a.sizeenglish= b.sizeenglish
from Products a , Conversions b
where b.sizedecimal= a.sizedecimal

the conversion table looks like this

1", 2.54
2 3/4", 5.625

the decimal sizes in my products table are already populated.
0
Comment
Question by:BoggyBayouBoy
  • 2
4 Comments
 
LVL 93

Accepted Solution

by:
Patrick Matthews earned 1200 total points
ID: 22967933
Hello BoggyBayouBoy,

UPDATE Products
SET sizeenglish = b.sizeenglish
FROM Products a INNER JOIN
      Conversions b ON a.sizeenglish = b.sizeenglish

Regards,

Patrick
0
 
LVL 9

Assisted Solution

by:Ernariash
Ernariash earned 800 total points
ID: 22968251
BoggyBayouBoy  you almost got it right :) just the alias, the matthewspatrick inner join is better, just review the ON as b.sizedecimal= a.sizedecimal
update a
set sizeenglish= b.sizeenglish
from Products a , Conversions b 
where b.sizedecimal= a.sizedecimal

Open in new window

0
 
LVL 9

Expert Comment

by:Ernariash
ID: 22968416
This is what I meant, and why I like Inner Join statement it makes the code
self-documenting, if you later have to go back and add a LEFT/RIGHT/FULL JOINs
to a third table, it will allow you not to get the mixed-join-types error, but both forms COMMAS and JOINS deliver the same result.
Your error "cannot be bound" is due to the alias your are updating Products and then in your from you have Products as a.. and yo do not need to use the alias in the set ...see set a.sizeenglish...see code :)
 

update a
set sizeenglish= b.sizeenglish
from Products a 
INNER JOIN  Conversions b 
ON b.sizedecimal= a.sizedecimal

Open in new window

0
 
LVL 1

Author Closing Comment

by:BoggyBayouBoy
ID: 31517117
Great!! thanks.
0

Featured Post

Get your Disaster Recovery as a Service basics

Disaster Recovery as a Service is one go-to solution that revolutionizes DR planning. Implementing DRaaS could be an efficient process, easily accessible to non-DR experts. Learn about monitoring, testing, executing failovers and failbacks to ensure a "healthy" DR environment.

Question has a verified solution.

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

In the first part of this tutorial we will cover the prerequisites for installing SQL Server vNext on Linux.
MSSQL DB-maintenance also needs implementation of multiple activities. However, unprecedented errors can hamper the database management. In that case, deploying Stellar SQL Database Toolkit ensures fast and accurate database and backup repair as wel…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.
Suggested Courses

810 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