Updating one table with values from another

Posted on 2008-11-15
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.
Question by:BoggyBayouBoy
    LVL 92

    Accepted Solution

    Hello BoggyBayouBoy,

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


    LVL 9

    Assisted Solution

    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

    LVL 9

    Expert Comment

    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

    LVL 1

    Author Closing Comment

    Great!! thanks.

    Featured Post

    PRTG Network Monitor: Intuitive Network Monitoring

    Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

    Join & Write a Comment

    Suggested Solutions

    This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
    The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
    Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
    Via a live example, show how to shrink a transaction log file down to a reasonable size.

    728 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

    20 Experts available now in Live!

    Get 1:1 Help Now