我在 oracle DB 中有兩個表，稱為collection和collection_h。我必須從collection_h 中洗掉所有記錄，這些記錄在集合表中具有相同的以下欄位。
我必須從 collection_h 表中洗掉由于以下查詢而出現的所有記錄：
select * from collection inner join collection_h on
collection.pos_protocol_id = collection_h.pos_protocol_id and
collection.terminal_pos_number = collection_h.terminal_pos_number and
collection.cb_file_number = collection_h.cb_file_number and
collection.cb_block_number = collection_h.cb_block_number and
collection.is_stan_batch = collection_h.is_stan_batch and
collection.is_transaction_date = collection_h.is_transaction_date and
collection.is_stan_trans = collection_h.is_stan_trans;
uj5u.com熱心網友回復：
洗掉存在的地方
delete
from collection as c
where exists (
select 1
from collection_h as h
where h.pos_protocol_id = c.pos_protocol_id
and h.terminal_pos_number = c.terminal_pos_number
and h.cb_file_number = c.cb_file_number
and h.cb_block_number = c.cb_block_number
and h.is_stan_batch = c.is_stan_batch
and h.is_transaction_date = c.is_transaction_date
and h.is_stan_trans = c.is_stan_trans
);
db<>fiddle here上的簡化測驗
但是如果列在匹配的行中可以有 NULL
delete
from collection as c
where exists (
select 1
from collection_h as h
where decode(h.pos_protocol_id, c.pos_protocol_id, 0, 1) = 0
and decode(h.terminal_pos_number, c.terminal_pos_number, 0, 1) = 0
and decode(h.cb_file_number, c.cb_file_number, 0, 1) = 0
and decode(h.cb_block_number, c.cb_block_number, 0, 1) = 0
and decode(h.is_stan_batch, c.is_stan_batch, 0, 1) = 0
and decode(h.is_transaction_date, c.is_transaction_date, 0, 1) = 0
and decode(h.is_stan_trans, c.is_stan_trans, 0, 1) = 0
);
uj5u.com熱心網友回復：
請在此處查看我所附的 excel 檔案
我使用這些型別的 excel 公式來避免錯誤。
uj5u.com熱心網友回復：
Delete x from collection X inner join collection_h Y on
X.pos_protocol_id = Y.pos_protocol_id and
X.terminal_pos_number = Y.terminal_pos_number and
X.cb_file_number = Y.cb_file_number and
X.cb_block_number = Y.cb_block_number and
X.is_stan_batch = Y.is_stan_batch and
X.is_transaction_date = Y.is_transaction_date and
X.is_stan_trans = Y.is_stan_trans;
轉載請註明出處，本文鏈接：https://www.uj5u.com/shujuku/376655.html
上一篇：OracleSQL：用于從固定格式的字串化映射中找出值的正則運算式
下一篇：使用輸出引數進行Oracle呼叫的Dapper給出了“PL/SQL：數字或值錯誤：DML回傳：寫入主機變數時出錯”