在DB2中，对大量行执行基于插入的更新

Question

在DB2中，我需要执行插入操作，然后使用该插入的结果/数据更新相关表。我需要在一百万条以上的记录上这样做，并且不希望锁定整个数据库。那么，1)我如何"耦合"插入和更新语句?2)我如何确保交易的完整性(不锁定整个系统)?

一些伪代码应该有助于澄清

步骤1

 insert into table1 (neededId, id)        select DYNAMICVALUE, id from tableX      where needed value is null

步骤2

 update table2 set neededId = (GET THE DYNAMIC VALUE JUST INSERTED)     where id = (THE ID JUST INSERTED)

注意:在table1中，ID col不是唯一的，所以我不能只是过滤，以找到新的DYNAMICVALUE

这应该更清楚(FTR，这是有效的，但我不喜欢它，因为我必须锁定表以保持完整性。如果我可以一起运行这些语句，并允许更新引用newAddressNumber值，那就太好了。

/****RUNNING TOP INSERT FIRST****/*
--insert a new address for each order that does not have a address id
insert into addresses
    (customerId, addressNumber, address)    
select 
    cust.Id, 
    --get next available addressNumber
    ifNull((select max(addy2.addressNumber) from addresses addy2 where  addy2.customerId = cust.id),0) + 1 as newAddressNumber,
    cust.address
from customers cust
where exists (
    --find all customers with at least 1 order where addressNumber is null
    select 1 from orders ord
    where 1=1
    and ord.customerId = cust.id
    and ord.addressNumber is null   
    )

/*****RUNNING THIS UPDATE SECOND*****/          
update orders ord1
set addressNumber = (
            select max(addressNumber) from addresses addy3 
            where addy3.customerId = ord1.customerId
            ) 
where 1=1 
    and ord1.addressNumber is null  

Answer 1

IDENTITY_VAL_LOCAL函数是一个非确定性函数，它返回标识列最近分配的值，该值是使用VALUES子句