SQL Server Performance

Help with update statement

Discussion in 'Getting Started' started by jul, May 29, 2007.

  1. jul New Member

    Hi guys,

    I have the following sample data:



    PaperID StatusID StatusDate StatusKey

    0001 4566 2003-09-03 00:00:00.000 D

    0001 4222 2003-09-03 00:00:00.000 C

    0001 4132 2003-09-01 00:00:00.000 A

    0002 4222 1999-04-14 00:00:00.000 C

    0002 4132 1999-04-10 00:00:00.000 A

    0003 4132 1986-08-03 00:00:00.000 A

    0003 4566 1986-07-29 00:00:00.000 D



    Now, if in the same paperID, there is a statusKy A and the status date is earlier than the other statusDate, i would like to change the other statusdate to be the same as the status date with the statusKy of 'A'. if there is a statusKy 'A', but the other Statusky contains dates that are earlier than the date in StatusKy 'A', then leave it as what it was.



    The result i would want to see :



    PaperID StatusID StatusDate StatusKey

    0001 4566 2003-09-01 00:00:00.000 D

    0001 4222 2003-09-01 00:00:00.000 C

    0001 4132 2003-09-01 00:00:00.000 A

    0002 4222 1999-04-10 00:00:00.000 C

    0002 4132 1999-04-10 00:00:00.000 A

    0003 4132 1986-08-03 00:00:00.000 A

    0003 4566 1986-07-29 00:00:00.000 D



    can you guys help me with this issue? i would appreciate it so much. thanks
  2. khtan New Member

    try this



    update o
    set StatusDate = a.StatusDate
    from tbl o inner join tbl a
    on o.PaperID = a.PaperID
    and o.StatusKey <> 'A'
    and a.StatusKey = 'A'
    where o.StatusDate > a.StatusDate



    KH

Share This Page