Oracle 更新语句以标记行

你好，问一下汤姆团队。

我正在使用下面的查询根据某些条件将行加载到目标数据库。完成此操作后，我想标记这些行，以便在下一个SSIS ETL运行中排除它们。

select t1.invoice_sender,t1.einvoice,t1.invoice_type,t3.INV_SENT_DATE,t3.APPROVALS,t1.LOADED_606
  FROM table1 t1
  LEFT JOIN table2 t2
  ON t1.ID = t2.ID
  INNER JOIN table3 t3
    ON (t3.invoice_sender = t1.invoice_sender AND t3.einvoice = t1.einvoice)
    AND t1.LOADED_606 = 0 --look for the rows not loaded (flagged as 0) in my ETL package
    AND t3.APPROVALS = 0 --lok for accepted approvals
    AND t3.INV_SENT_DATE < TRUNC(SYSDATE)+1 AND t3.INV_SENT_DATE >=
  (CASE  
   WHEN TO_NUMBER(TO_CHAR(SYSDATE,'DD')) <= 15 --If today's date is equal or less than day 15 of current month
   THEN TRUNC(SYSDATE-20,'MM') -- then remove 20 days (which puts me *somewhere* in last month, and then truncate down the 1st of that month)
   ELSE TRUNC(SYSDATE,'MM') --otherwise just truncate down to the start of the current month. 
   END)  --end
    AND (t1.invoice_type NOT IN (32,41,43) OR substr(t2.modified_einvoice,2,2) not in (02,32)); --e-invoice types 32, 41 and 43 and modified einvoices 02 and 32 do not apply

我可以使用什么update语句将目标数据库的加载行标记为null？t1.LOADED _ 606 = 0是标志列

提前感谢。

完全猜测后，您可以获取上面查询的结果，返回要更新的表的主键。

并使它成为您更新的where子句中的嵌套子查询。

例如:

update table1
set  processed = ...
where primary_key = ( 
  select table1.primary_key
  from   
)

但是我不清楚如果没有完整的测试用例，这应该如何工作。这包括:

-创建表格
-插入数据
-运行更新后您期望的结果