Update rows

  • Hi,

    I need help with the following:

    Here's the sample of the two tables .

    I want to update column BP_AT_time in Table1 by BP_Code from Table2.

    When is equal there's no problem.

    When I don't have match by a.bp_period< b.period then I don't know how to do it

    I want to use row from Table2 next greater then BP_period from table 1

    In this scenario processing I would use 901211 to update table 1 because the first greather then

    table1

    200402 640559 NULL

    table2

    200411 640559 901211

     

    update table1

    set BP_At_Time = b.BP_Code

    from table1 a, table2 b

    where a.matter_code=b.matter_code

    and a.bp_period=b.period

    Table1

    BP_Period Matter_Code BP_At_Time

    ------------- -------------------- -----------

    200301 640559 NULL

    200402 640559 NULL

    200503 640559 NULL

    200504 640559 NULL

    200605 640559 NULL

    200606 640559 NULL

    Table2

    period matter_code BP_Code

    ------ ---------------- -------

    200312 640559 901034

    200411 640559 901211

    200503 640559 901035

    200510 640559 901036

    200607 640559 901037

    Results needed

    Period_yyyymm Matter_Code BP_At_Time

    ------------- -------------------- -----------

    200301 640559 901034

    200402 640559 901211

    200503 640559 901035

    200504 640559 901036

    200605 640559 901037

    200606 640559 901037

    Thanks in advance.

     

    David

  • Try this:

    DECLARE @Table1 table(BP_Period int, Matter_Code int, BP_At_Time int NULL)

    INSERT @table1 values(200301, 640559, NULL)

    INSERT @table1 values(200402, 640559, NULL)

    INSERT @table1 values(200503, 640559, NULL)

    INSERT @table1 values(200504, 640559, NULL)

    INSERT @table1 values(200605, 640559, NULL)

    INSERT @table1 values(200606, 640559, NULL)

    DECLARE @Table2 table(Period int, Matter_Code int, BP_Code int)

    INSERT @table2 values(200312, 640559, 901034)

    INSERT @table2 values(200411, 640559, 901211)

    INSERT @table2 values(200503, 640559, 901035)

    INSERT @table2 values(200510, 640559, 901036)

    INSERT @table2 values(200607, 640559, 901037)

     

    UPDATE t1

    set t1.BP_At_Time = t3.BP_Code

    from @table1 t1

    INNER JOIN (select a.matter_code, a.bp_period, min(b.period) period

                FROM @table1 a

                INNER JOIN @table2 b

                ON a.matter_code=b.matter_code AND b.period >= a.bp_period

                GROUP BY a.matter_code, a.bp_period

                ) t2

    ON t1.matter_code=t2.matter_code and t1.bp_period=t2.bp_period

    INNER JOIN @table2 t3

    ON t1.matter_code=t3.matter_code and t2.period=t3.period

    SELECT * FROM @table1

  • update

    a

    set

    BP_At_Time = b.BP_Code

    from

    table1 a

    join

    (select a.matter_code, a.bp_period, min(b.period) period

    from table1 a, table2 b where a.matter_code=b.matter_code
    and a.bp_period<=b.period
    group by a.matter_code, a.bp_period

    )

    V

    on

    a.matter_code = V.matter_code

    and

    a.bp_period = V.bp_period

    join

    table2 b

    on

    a.matter_code=b.matter_code

    and

    a.period = b.period

     

    --this will work as long as the combination {matter_code,period} is unique in table2.

    [edit - looks like someone else got there first. My warning above still applies.]

    Tim Wilkinson

    "If it doesn't work in practice, you're using the wrong theory"
    - Immanuel Kant

  •  Jeff, Thanks alot it worked.

  • Tim,

    Thanks. Worked.

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply