title: Updating A Table Based On A Dependent Table
body: <p>The Setup</p>
  
  <hr>
  
  <p>I have two tables, <code>USERS</code> and <code>PETS</code>.</p>
  
  <p>each user has an <code>ID</code>, a <code>NAME</code>, and a flag <code>HAS_PETS</code>, which is 1 if the user owns at least one pet, and 0 otherwise.</p>
  
  <p>Each <code>pet</code> has an <code>ID</code>, a <code>NAME</code>, and an <code>OWNER_ID</code>. Here is the creation script:</p>
  
  <pre><code>create table USERS(
  ID NUMBER(*,0),
  NAME VARCHAR2(50 BYTE),
  HAS_PETS NUMBER(1,0),
  CONSTRAINT "XPKUSERS" PRIMARY KEY ("ID")
  );
  
  create table PETS(
  ID NUMBER(*,0),
  NAME VARCHAR2(50 BYTE),
  OWNER_ID NUMBER(*,0),
  CONSTRAINT "XPKPETS" PRIMARY KEY ("ID")
  );
  
  insert into USERS values(0, 'Alice', 0);
  insert into USERS values(1, 'Bob', 1);
  insert into USERS values(2, 'Carol', 0);
  
  insert into PETS values(0, 'Fido', 1);
  insert into PETS values(1, 'Spot', 1);
  insert into PETS values(2, 'Xerxes', 2);
  </code></pre>
  
  <p>The Problem</p>
  
  <hr>
  
  <p>At one point, many pets were added to <code>PETS</code>, but the <code>OWNER</code> table's <code>HAS_PETS</code> flag was not updated. In the sample data above, Carol owns Xerxes, but her flag is 0.</p>
  
  <p>I want to write a statement which will set <code>HAS_PETS</code> to true if the owner has a pet. With the above data, it should set Carol's <code>HAS_PETS</code> flag to 1. 
  I tried this:</p>
  
  <pre><code>update (
  select * 
  from USERS a join PETS b 
  on a.HAS_PETS = 0 and a.ID = b.OWNER_ID
  )
  set HAS_PETS = 1 
  </code></pre>
  
  <p>but it gives an error, <code>cannot modify a column which maps to a non key-preserved table</code>. Oracle recommends that I <code>Modify the underlying base tables directly</code>, but I can't identify which rows need updating unless I do this join.</p>
  
  <p>Googling around prompted me to try this alternative syntax:</p>
  
  <pre><code>update a
  set a.HAS_PETS = 1
  from USERS a join PETS b 
  on a.HAS_PETS = 0 and a.ID = b.OWNER_ID
  </code></pre>
  
  <p>but it gives an error, <code>SQL command not properly ended</code>, and Wikipedia says "Some databases allow the non-standard use of the FROM clause", leading me to believe this isn't supported by Oracle.</p>
  
  <h2>The Questions</h2>
  
  <ul>
  <li>What is the meaning of the first error? HAS_PETS belongs to USERS, which has a primary key. I don't see any non key-preserved tables here.</li>
  <li>How can I modify my statement so that it executes the way I expect it to?</li>
  </ul>