title: SQL - updating Oracle 10g database from a complex query
body: <p>trying to update a single column based on criteria.  The rows that need to be updated are returned by the query</p>
  
  <pre><code>SELECT 
  it.objectid,
  it.versionnumber,
  it.itemnumber "Item Number", 
  it.itemtype,
  itcovprem.basisofsettlement,
  itcovprem.coverage "Coverage"
  FROM 
  itemcoveragesandpremiums itcovprem,
  items it,
  policies pl
  WHERE 
  pl.objectid = it.parentobjectid AND 
  pl.policytype in ('H','F') AND
  it.objectid = itcovprem.itemobjectid AND
  it.itemtype in ('SHOA','SHOB','SHOC','SHOD','SHOK','SHOL') and
  it.versionnumber = itcovprem.itemversionnumber AND
  ((itcovprem.coverage like ('A - Dwg Bldg%')) or
  (itcovprem.coverage like ('#42 - Repl Cost Plus%')) or
  (itcovprem.coverage like ('#42 - Limited GRC%'))) and
  it.effectivedate &gt;= TO_DATE('01-JAN-2006', 'DD-MON-YYYY') and
  exists (select * from itemcoveragesandpremiums icp where icp.itemobjectid = it.objectid and icp.itemversionnumber = it.versionnumber and icp.coverage like ('#42%')) and
  itcovprem.basisofsettlement not in ('LGRC')
  </code></pre>
  
  <p>I've tried a few options to convert this to an update query, but no joy.  </p>
  
  <p>I get Error - line 3 - SQL command not properly ended when using</p>
  
  <pre><code>update itemcoveragesandpremiums
  set basisofsettlement = 'LGRC'
  from itemcoveragesandpremiums as itcovprem
  inner join items as it
  on it.objectid = itcovprem.itemobjectid and it.versionnumber = itcovprem.itemversionnumber
  inner join policies as pl
  on pl.objectid = it.parentobjectid 
  where [cut, rest of query was below]
  </code></pre>
  
  <p>I get Error - line 6 - missing right parenthesis when trying use an inline query</p>
  
  <pre><code>update
  (
  SELECT 
  itcovprem.basisofsettlement as OLD
  FROM 
  itemcoveragesandpremiums as itcovprem inner join items as it on it.objectid = itcovprem.itemobjectid and it.versionnumber = itcovprem.itemversionnumber inner join policies AS pl on pl.objectid = it.parentobjectid 
  WHERE [query snipped]
  ) t
  set t.old = 'LGRC'
  </code></pre>
  
  <p>Seems that SQL*Plus just wants to stop looking at the update before it gets to the meat of my select query.  I'm not sure if I'm formatting it incorrectly or going at it from the wrong direction.  Any advice?</p>