Insert data from one table into another table RRS feed

  • Question

  • I need to include data from one table to another. What I need is to take the info from a field in Table A and insert that into a field in Table B based on matching info in another field.

    Basicly I ahve the following scenario.

    Table ProductVariant with fields ProductID and DisplayOrder and Table ProductCategory with fields ProductID and DisplayOrder

    I need to include the DisplayOrder info from Table ProductVariant into field DisplayOrder in Table ProductCategory based on a match in field ProductID

    so If ProductVariant has the following records
    ProductID  DisplayOrder
    1000               12
    1001               25
    1002               42

    And ProductCategory has
    ProductID  DisplayOrder
    1000               1
    1001               1
    1002               1

    The DisplayOrder field in ProductCategory should be updated to look like

    ProductID  DisplayOrder
    1000               12
    1001               25
    1002               42

    I have used SET to change values in field such as changing prices by using

    UPDATE ProductVariant
    SET Price = Price*1.05

    to increase prices by 5% but cannot figure and have also used it to include text from one field to another within the same table but I cannot figure out how to do it when data needs to come from a different table and needs to be qualified based on yet another field

    Any suggestions or directions to info that will help me. Everything I have come across so far is for more complex needs and doesn't suit my level of experience with SQL. I am using SQL 2005 Enterprise Edition



    Tuesday, November 28, 2006 12:21 AM


  • Hello,

    You would:

    update ProductCategory
    set DisplayOrder = pc.DisplayOrder
    from ProductVariant pv
    join ProductCategory pc on pv.ProductID = pc.ProductID


    Tuesday, November 28, 2006 1:50 AM