CREATE TABLE TEST_MAIN(
ID NUMBER,
VALUE NUMBER,
PRIMARY KEY(ID)
)CREATE TABLE TEST_SUB1(
ID NUMBER,
VALUE1 NUMBER,
MAIN_ID NUMBER,
PRIMARY KEY(ID)
)
CREATE TABLE TEST_SUB2(
ID NUMBER,
VALUE2 NUMBER,
MAIN_ID NUMBER,
PRIMARY KEY(ID)
)
更新TEST_MAIN表中的字段VALUE为TEST_SUB1中的VALUE1与TEST_SUB2中的VALUE2之和!
value = (select a.value1 + b.value2
from test_sub1 a
full join test_sub2 b
on a.main_id = b.main_id)
实验过的哦。
UPDATE test_main t
SET VALUE =
(SELECT a.value1 + b.value2
FROM test_sub1 a, test_sub2 b
WHERE a.main_id = b.main_id
AND a.main_id = t.id)
WHERE EXISTS (SELECT 1
FROM test_sub1 a, test_sub2 b
WHERE a.main_id = b.main_id
AND a.main_id = t.id);
--2.
MERGE INTO test_main t
USING (SELECT a.value1 + b.value2 value1
FROM test_sub1 a, test_sub2 b
WHERE a.main_id = b.main_id) c
ON (t.id = c.main_id)
WHEN MATCHED THEN
UPDATE SET t.value = c.value1;