title: Oracle - update join - non key-preserved table
body: <p>I'm trying to replicate an Ingres "update tbl1 from tbl2" command, which doesn't exactly exist in Oracle.</p>
  
  <p>So I use "update (select tbl1 join tbl2...)" command.  Both tables have primary keys defined and I <em>thought</em> that my join was uniquely identifying rows, but I'm still getting "ORA-01779: cannot modify a column which maps to a non key-preserved table".</p>
  
  <p>Here are suitably anonymized table definitions and the update I'm trying to execute:</p>
  
  <pre><code>CREATE TABLE tbl1
  (
     ID decimal(11) NOT NULL,
     A varchar2(3) NOT NULL,
     B float(7),
     CONSTRAINT tbl1_pk PRIMARY KEY (ID,A)
  )
  ;
  
  CREATE TABLE tbl2
  (
     ID decimal(11) NOT NULL,
     A varchar2(3) NOT NULL,
     B float(15),
     C float(15),
     D char(1) NOT NULL,
     CONSTRAINT tbl2_PK PRIMARY KEY (ID,A,D)
  )
  ;
  
  UPDATE 
    (select tbl1.b, tbl2.c 
     from tbl1 inner join tbl2 
     on tbl1.id=tbl2.id 
     and tbl1.a=tbl2.a 
     and tbl1.b=tbl2.b 
     and tbl1.a='foo' 
     and tbl2.D='a') 
  set b=c;
  </code></pre>
  
  <p>How can I define my select such that Oracle will be satisfied that I have no uniqueness violations?</p>