Table Lookup


DevX Home    Today's Headlines   Articles Archive   Tip Bank   Forums   

Results 1 to 3 of 3

Thread: Table Lookup

  1. #1
    Dan Guest

    Table Lookup


    I have a table named Product with a primary key called ProductID and an empty
    column named NewColumn (along with several unrelated fields). I have a lookup
    table with a column named ProductID and another column named NewColumn. I'm
    trying to get the new data from the lookup table into the existing Product
    table. Every time I come up with this situation, I'm thick as a brick and
    I can't figure out how to do it.

    Can anyone show me how to do this with a query? I'm currently doing it longhand
    using a stored procedure that reads through the lookup table one row at a
    time.

    Thanks,

    Dan

  2. #2
    Simon Sellick Guest

    Re: Table Lookup


    Dan,
    Try:
    UPDATE Table
    SET NewCol =
    SELECT NewCol
    FROM LookupTable
    WHERE LookupTable.ProductId = Table.ProductId

    I think that's what you're after. I haven't tested it (server not available)
    so apologies if the syntax is incorrect.
    Hope this helps,
    Simon.

    "Dan" <dan.clem@trilogynetworks.com> wrote:
    >
    >I have a table named Product with a primary key called ProductID and an

    empty
    >column named NewColumn (along with several unrelated fields). I have a lookup
    >table with a column named ProductID and another column named NewColumn.

    I'm
    >trying to get the new data from the lookup table into the existing Product
    >table. Every time I come up with this situation, I'm thick as a brick and
    >I can't figure out how to do it.
    >
    >Can anyone show me how to do this with a query? I'm currently doing it longhand
    >using a stored procedure that reads through the lookup table one row at

    a
    >time.
    >
    >Thanks,
    >
    >Dan



  3. #3
    Dan Guest

    Re: Table Lookup


    I had to add parentheses around the SELECT statement, and then this worked
    perfectly. Thanks much!

    Dan

    "Simon Sellick" <simon.sellick@tesco.net> wrote:
    >
    >Dan,
    >Try:
    > UPDATE Table
    > SET NewCol =
    > (SELECT NewCol
    > FROM LookupTable
    > WHERE LookupTable.ProductId = Table.ProductId)
    >
    >I think that's what you're after. I haven't tested it (server not available)
    >so apologies if the syntax is incorrect.
    >Hope this helps,
    >Simon.
    >
    >"Dan" <dan.clem@trilogynetworks.com> wrote:
    >>
    >>I have a table named Product with a primary key called ProductID and an

    >empty
    >>column named NewColumn (along with several unrelated fields). I have a

    lookup
    >>table with a column named ProductID and another column named NewColumn.

    >I'm
    >>trying to get the new data from the lookup table into the existing Product
    >>table.


Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
HTML5 Development Center
 
 
FAQ
Latest Articles
Java
.NET
XML
Database
Enterprise
Questions? Contact us.
C++
Web Development
Wireless
Latest Tips
Open Source


   Development Centers

   -- Android Development Center
   -- Cloud Development Project Center
   -- HTML5 Development Center
   -- Windows Mobile Development Center