update某個表時，如果需要和其他表關聯，這樣的SQL語句在ORACLE中實現起來比較嘔心。各個where條件需要分別與一堆select語句嵌套使用才可，暈~~
以下sql語句是在工作中寫出的，做個備忘錄：
-- 更新應收賬款淨額
update t_0303009 t1
set t1.item_value =
(
select t2.item_value
from
(
SELECT a.product_id, a.item_id, b.item_value
FROM (SELECT product_id, item_id, item_value
FROM t_0303009
WHERE item_id LIKE 'B%011') a,
(SELECT product_id, item_id, item_value
FROM t_0303009
WHERE item_id LIKE 'B%008') b
WHERE a.product_id = b.product_id
AND SUBSTR (a.item_id, 2, 6) = SUBSTR (b.item_id, 2, 6)
AND a.item_value = 0
) t2
where t1.product_id = t2.product_id
and t1.item_id = t2.item_id
)
where t1.product_id = ( select product_id from
(
SELECT a.product_id, a.item_id, b.item_value
FROM (SELECT product_id, item_id, item_value
FROM t_0303009
WHERE item_id LIKE 'B%011') a,
(SELECT product_id, item_id, item_value
FROM t_0303009
WHERE item_id LIKE 'B%008') b
WHERE a.product_id = b.product_id
AND SUBSTR (a.item_id, 2, 6) = SUBSTR (b.item_id, 2, 6)
AND a.item_value = 0
) jj
where t1.product_id = jj.product_id
and t1.item_id = jj.item_id
)
and t1.item_id = ( select item_id from
(
SELECT a.product_id, a.item_id, b.item_value
FROM (SELECT product_id, item_id, item_value
FROM t_0303009
WHERE item_id LIKE 'B%011') a,
(SELECT product_id, item_id, item_value
FROM t_0303009
WHERE item_id LIKE 'B%008') b
WHERE a.product_id = b.product_id
AND SUBSTR (a.item_id, 2, 6) = SUBSTR (b.item_id, 2, 6)
AND a.item_value = 0
) jj2
where t1.product_id = jj2.product_id
and t1.item_id = jj2.item_id
)
