UPDATE all values of a column with a single value, if that value is present, for all users

Question

I have table like this:

ID | cur_val | done  | res 
1  | 10000   | false | false
2  | 20000   | false | false
3  | 30000   | false | false
1  | 40000   | false | false
3  | 60000   | false | false
1  | 10000   | true  | false

I need an update query to update the res column (which is false by default) depending on the values from the done column. Specifically, for any ID, if done is true, then all the res values associated with that ID should be updated to true.

The final table should look like this:

ID | cur_val | done  | res 
1  | 10000   | false | true
2  | 20000   | false | false
3  | 30000   | false | false
1  | 40000   | false | true
3  | 60000   | false | false
1  | 10000   | true  | true

I'd appreciate any help with this.

Answer 1

in sql server

UPDATE t1
SET res = 1
FROM yourtable t1
WHERE EXISTS
    (SELECT 1
     FROM yourtable t2
     WHERE t1.ID = t2.ID
       AND done = 1)

in postgres

UPDATE yourtable t1
SET res = true
WHERE EXISTS
    (SELECT 1
     FROM yourtable t2
     WHERE t1.id = t2.id
       AND done = true)
;