title: How to use UPDATE and WITH in PLSQL
body: <p>Does anyone have a sample of how to use WITH and UPDATE in a query in PLSQL?</p>
  
  <p>I binged and googled without success.</p>
  
  <p>Here is what I am trying to do</p>
  
  <pre><code>WITH
  OldRecords AS
  (
    SELECT 'New Records' as RecordLabel, rowid, OrDev.* 
    FROM CDR.MSRS_OR_ORDEV OrDev
    WHERE 1=1
      AND OrDev.ASOFENDDATETIME IS NULL
      AND OrDev.REPORTSTARTDATE = to_date ('7/1/2010', 'mm/dd/yyyy')
      AND OrDev.REPORTENDDATE = to_date ('7/31/2010', 'mm/dd/yyyy')
      AND OrDev.downloadrequestid &lt;&gt; 3
  ), 
  NewRecords AS
  (
    SELECT 'Old Records' as RecordLabelrowid, OrDev.* 
    FROM CDR.MSRS_OR_ORDEV OrDev
    WHERE 1=1
      AND OrDev.ASOFENDDATETIME IS NULL
      AND OrDev.REPORTSTARTDATE = to_date ('7/1/2010', 'mm/dd/yyyy')
      AND OrDev.REPORTENDDATE = to_date ('7/31/2010', 'mm/dd/yyyy')
      AND OrDev.downloadrequestid = 3
  )
  UPDATE CDR.MSRS_OR_ORDEV SET ASOFENDDATETIME = GETDATE()
  WHERE RowID IN
  (
    SELECT OldRecords.RowId
    FROM OldRecords
      INNER JOIN NewRecords
        ON  OldRecords.Customer_Id = NewRecords.Customer_Id
        AND OldRecords.BusinesKey1 = NewRecords.BusinesKey1
        AND OldRecords.BusinesKey2 = NewRecords.BusinesKey2
  )
  </code></pre>
  
  <p>Basically this is a Type-II dimension update.</p>