Oracle 使用多对1链表关系更新记录

我有一个MS_ACCESS查询要转换为Oracle SQL

访问查询

UPDATE target_table T 
INNER JOIN source_table S 
ON T.linkcolumn = S.linkColumn
SET 
T.field1 = S.field1, 
T.field2 = S.field2,
T.field3 = S.field3;

注意: 即使源 (1) 和目标 (很多) 之间存在一对多的关系，此查询也会更新记录
----------------------------------------------------

Oracle SQL
此代码会产生 “无法获取稳定的行集” 错误，因为
目标表中的许多记录的实例与源表中的一条记录匹配

MERGE INTO target_table T
USING ( 
    SELECT *
    FROM source_table 
    ) S
ON   
(
T.manyRecsLinkColumn = S.oneRecLinkColumn  
)
WHEN MATCHED THEN 
UPDATE SET 
    T.columnToUpdate1 = S.Value1,
    T.columnToUpdate2 = S.Value2,
    T.columnToUpdate3 = S.Value3

-------------------------------------------------------

使用VBA脚本 (伪代码) 访问中的替代方法

Dim strFieldValue as String
Dim myDB as database
Dim rsSource, rsTarget as Recordset

Set rsSource = myDB.openRecordset(source_table)
Set rsTarget = myDB.openRecordset(target_table, dbOpenDynaset)

rsSource.moveFIRST     ' Start at 1st record
Do while not rsSource.EOF     ' Begin Row by Row Scan
 strFieldValue = rs!Source!MyValue
 rsTarget.findFIRST " rsTarget.FieldName  = 'strFieldValue' "
 if NOT rsTarget.noMatch    ' it is a match
  Update rsTarget!Column1 = rsSource!Column1
  Update rsTarget!Column2 = rsSource!Column2
  Update rsTarget!Column3 = rsSource!Column3
 else         ' it is not a match
  Goto NextRecord
 endif
LookForMore:
 rsTarget.findNEXT " rsTarget.FieldName  = 'strFieldValue' "
 if NOT rsTarget.noMATCH    ' it is a match
  Update rsTarget!Column1 = rsSource!Column1
  Update rsTarget!Column2 = rsSource!Column2
  Update rsTarget!Column3 = rsSource!Column3
  Goto LookForMore
 endif
NextRecord:      ' no more matches
 rsSource.moveNEXT    ' Move to next Source record
Loop       ' Repeat until End-of-Table

-

问题:

1. Is there an Oracle SQLstatement, perhaps a refinement of the Oracle Code above, which will Update multiple records when there is
源 (1) 和目标 (多) 之间的一对多关系

2.如果存储过程是唯一的方法，您是否可以提供该过程的一般外观，该外观可能类似于上面的ACCESS VBA脚本。

因此，您的源表中有一行与目标中的许多行匹配？

您可以在Oracle数据库中 “更新联接”。

如果您在联接列上有一个主键或唯一键!

create table t1 (
  x int,
  c1 int,
  c2 int, 
  c3 int
);
create table t2 (
  x int ,
  c1 int,
  c2 int, 
  c3 int
);

insert into t1 values (1, 1, 1, 1);
insert into t2 values (1, 0, 0, 0);
insert into t2 values (1, 2, 2, 2);
insert into t2 values (1, 4, 4, 4);
commit;

select * from t2;

X  C1  C2  C3  
1  0   0   0   
1  2   2   2   
1  4   4   4  

update (
  select t1.c1 c11, t2.c1 c12 ,
         t1.c2 c21, t2.c2 c22 ,
         t1.c3 c31, t2.c3 c32 
  from   t1 join t2 on t1.x = t2.x
)
set c12 = c11, c22 = c21, c32 = c31;

SQL Error: ORA-01779: cannot modify a column which maps to a non key-preserved table

select * from t2;

X  C1  C2  C3  
1  0   0   0   
1  2   2   2   
1  4   4   4 

alter table t1 add primary key (x);

update (
  select t1.c1 c11, t2.c1 c12 ,
         t1.c2 c21, t2.c2 c22 ,
         t1.c3 c31, t2.c3 c32 
  from   t1 join t2 on t1.x = t2.x
)
set c12 = c11, c22 = c21, c32 = c31;

select * from t2;

X  C1  C2  C3  
1  1   1   1   
1  1   1   1   
1  1   1   1 

或者你可以去一个相关的更新:

rollback;

update t2
set    (c1, c2, c3) = (
  select c1, c2, c3 from t1
  where  t1.x = t2.x
);

select * from t2;

X  C1  C2  C3  
1  1   1   1   
1  1   1   1   
1  1   1   1