如何在PLSQL中使用UPDATE和WITH

这是如何在PLSQL的更新中使用的。请注意,这只适用于单列更新。

UPDATE CDR.MSRS_OR_ORDEV 
SET ASOFENDDATETIME = sysdate
WHERE RowID IN
(
  WITH
  OldRecords AS
  (
    SELECT rowid, OldRecords.* 
    FROM CDR.MSRS_OR_ORDEV OldRecords
    WHERE 1=1
      AND OldRecords.ASOFENDDATETIME IS NULL
      AND OldRecords.REPORTSTARTDATE = to_date ('7/1/2010', 'mm/dd/yyyy')
      AND OldRecords.REPORTENDDATE = to_date ('7/31/2010', 'mm/dd/yyyy')
      AND OldRecords.downloadrequestid <> 3
  ), 
  NewRecords AS
  (
    SELECT rowid, NewRecords.* 
    FROM CDR.MSRS_OR_ORDEV NewRecords
    WHERE 1=1
      AND NewRecords.ASOFENDDATETIME IS NULL
      AND NewRecords.REPORTSTARTDATE = to_date ('7/1/2010', 'mm/dd/yyyy')
      AND NewRecords.REPORTENDDATE = to_date ('7/31/2010', 'mm/dd/yyyy')
      AND NewRecords.downloadrequestid = 3
  )
  SELECT OldRecords.RowId
  FROM OldRecords
    INNER JOIN NewRecords
      ON  OldRecords.BusinesKey1 = NewRecords.BusinesKey1
      AND OldRecords.BusinesKey2 = NewRecords.BusinesKey2
);

查看的语法图 update 和 select ,我不确定子查询因子是否可以用于update。您可以将这些查询完全替换回UPDATE语句的子查询部分,也可以为它们创建视图。我同意你的观点。