locked
update value based on avalue in the same table RRS feed

  • Question

  • User496086301 posted

    I am trying to do this for 1000+ records. wonder what is the best way.

    I would like to update the value of c where a='X' and year=2014 and update in C  when year(2015)

    select c from table where a in (x,y,z) and year(b)=2014
    update table set c=(from above select) where a in (x,y,z) and year(b)=2015

    Monday, November 23, 2015 10:29 PM

Answers

  • User-808054615 posted

    Try something like this:

    update t
    set t.c = u.c
    from Table as t
    inner join Table as u
        on u.a = t.a
    where
        t.a in ('x', 'y', 'z') and
        year(t.b) = 2015 and
        year(u.b) = 2014

    Hope this helps.

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Tuesday, November 24, 2015 11:16 AM
  • User77042963 posted
    update table set c=(select top 1 c from table where a in (x,y,z) and year(b)=2014) 
    where a in (x,y,z) and year(b)=2015

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Tuesday, November 24, 2015 2:29 PM

All replies

  • User-808054615 posted

    Try something like this:

    update t
    set t.c = u.c
    from Table as t
    inner join Table as u
        on u.a = t.a
    where
        t.a in ('x', 'y', 'z') and
        year(t.b) = 2015 and
        year(u.b) = 2014

    Hope this helps.

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Tuesday, November 24, 2015 11:16 AM
  • User77042963 posted
    update table set c=(select top 1 c from table where a in (x,y,z) and year(b)=2014) 
    where a in (x,y,z) and year(b)=2015

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Tuesday, November 24, 2015 2:29 PM