Oracle - update join - non key-preserved table

You should be able to do this with a correlated subquery

UPDATE tbl1 t1
   SET t1.b = (SELECT c
                 FROM tbl2 t2
                WHERE t1.id = t2.id
                  AND t1.a  = t2.a
                  AND t1.b  = t2.b
                  AND t2.d  = 'a')
 WHERE t1.a = 'foo'
   AND EXISTS( SELECT 1
                 FROM tbl2 t2
                WHERE t1.id = t2.id
                  AND t1.a  = t2.a
                  AND t1.b  = t2.b
                  AND t2.d  = 'a')

The problem with the UPDATE that you've written is that Oracle cannot guarantee that there is exactly 1 tbl2.c value that corresponds to a single tbl1.b value. If there are multiple rows in tbl2 for any particular row in tbl1, the correlated update is going to throw an error indicating that a single-row subquery returned multiple rows. In that case, you'd need to add some logic to the subquery to specify which row from tbl2 to use in that case.

This statement fails with an error (ORA-01779 cannot modify a column which maps to a non key-preserved table), because it attempts to modify the base tbl1table, and the tbl1 table is not key-preserved in the view . because although (ID,A) is a key of the dept table, it is not a key of the join.