Search code examples
sqlsql-serversybase

How to update a record by checking a value in another record in same table

I have a table:

ID      DATE            RANGE       STATUS
--------------------------------------------
123     30-SEP-2019     000         C
123     30-SEP-2019     001         NULL
345     25-SEP-2019     000         C
345     30-SEP-2019     001         NULL
567     30-SEP-2019     000         C
567     30-SEP-2019     001         NULL
789     27-SEP-2019     000         C
789     30-SEP-2019     001         NULL

Records with the same ID (for ex., 123) considered as same record with different RANGE. I need to update the STATUS of 001 RANGE records to Y for which 000 RANGE record should have the date as 30-SEP-2019.

Ex. Update query should update the STATUS to 'Y' for the record with the ID - '123' and RANGE - '001', because for the ID - '123' with the RANGE - '000' record's DATE is '30-SEP-2019'. Also it should update the STATUS of 001 RANGE record with the ID - 567.

It should not update the STATUS of 001 RANGE for the IDs 789 & 345, because those 000 RANGE record's DATE is not equal to 30-SEP-2019.

How can I achieve this?

Solution

  • A correlated subquery with filtering comes to mind:

    update t
    set status = (select t2.status
                  from t t2
                  where t2.id = t.id and t2.range = '000'
                 )
    where date = '2019-09-30' and range = '001';