Q:

Update multiple rows based on values in other rows in same table

I need to update multiple rows based on value in other rows with matching id.  
Table structure:
ID   |    Sub-ID | value
----------------------------
1    |      1    |    a
1    |      2    |    b
1    |      3    |    c
2    |      1    |    x
2    |      2    |    y
2    |      3    |    z
3    |      1    |    k
3    |      2    |    l
3    |      3    |    m

I need to update value of SubID = 2 with value of SubId=3 for  a specific IDs (where ID in other table )
The result should be (base on the above):
ID   |    Sub-ID | value
----------------------------
1    |      1    |    a
1    |      2    |    c
1    |      3    |    c
2    |      1    |    x
2    |      2    |    y
2    |      3    |    z
3    |      1    |    k
3    |      2    |    m
3    |      3    |    m

What will be the most efficient way to implement it?
This what I have right now:
UPDATE data_tab tab1
   SET (value) =
          (SELECT tab2.value
             FROM data_tab tab2
            WHERE tab1.id = tab2.id 
              AND tab1.sub_id = 2 AND tab2.sub_id = 3 
              )      
 WHERE EXISTS (SELECT 1    FROM ids_table 
                WHERE id = tab1.id)

A:

The answer to your question is something like this:
UPDATE data_tab tab1
   SET value = (SELECT tab2.value
                FROM data_tab tab2
                WHERE tab1.id = tab2.id AND
                      tab2.sub_id = 3
              )
   WHERE tab1.id in (select id from ids_table) and
         tab1.sub_id = 2;

In other words, your original query is fine.  I think it is more efficient to move the condition on sub_id = 2 to the outer query.

