17,377
社区成员
发帖
与我相关
我的任务
分享
update tbloutadjustdetail T1 set(FBillDetailID, FSerialNo) = (select d.FID, d.FSerialNo From tblProductOut m,tblProductOutDetail d WHERE m.FID = d.FheaderID --连接m d AND m.FDate = T1.FInDate --内嵌表要引用更新表的名称tbloutadjustdetail And m.FNo = T1.FInNo --内嵌表要引用更新表的名称tbloutadjustdetail And d.FStockID = T1.FStockID --内嵌表要引用更新表的名称tbloutadjustdetail And d.FPrice = T1.FOldPrice) --内嵌表要引用更新表的名称tbloutadjustdetail And T1.FBillDetailID is null)
WHERE EXISTS
(
SELECT 1 FROM
tblProductOut m,tblProductOutDetail d WHERE m.FID = d.FheaderID --连接m d AND m.FDate = T1.FInDate --内嵌表要引用更新表的名称tbloutadjustdetail And m.FNo = T1.FInNo --内嵌表要引用更新表的名称tbloutadjustdetail And d.FStockID = T1.FStockID --内嵌表要引用更新表的名称tbloutadjustdetail And d.FPrice = T1.FOldPrice) --内嵌表要引用更新表的名称tbloutadjustdetail And T1.FBillDetailID is null
)
update tbloutadjustdetail T1 set(FBillDetailID, FSerialNo) =
(select d.FID, d.FSerialNo
From tblProductOut m,tblProductOutDetail d
WHERE
m.FID = d.FheaderID --连接m d
AND m.FDate = T1.FInDate --内嵌表要引用更新表的名称tbloutadjustdetail
And m.FNo = T1.FInNo --内嵌表要引用更新表的名称tbloutadjustdetail
And d.FStockID = T1.FStockID --内嵌表要引用更新表的名称tbloutadjustdetail
And d.FPrice = T1.FOldPrice) --内嵌表要引用更新表的名称tbloutadjustdetail
And T1.FBillDetailID is null;
)
更新的表A只能出现一次(包括子查询),如果需要则引用UPDATE后面的表名即可
no
name
name2
BnoID
Bname
update A set A.col=(select .....)
where exists (select ...)
--这样也可以实现更新
UPDATE tbloutadjustdetail adjust
SET (adjust.FBillDetailID,adjust.FSerialNo)==( SELECT d.FID,d.FSerialNo
FROM tblProductOutDetail d,tblProductOut m
WHERE m.FID = d.FheaderID AND m.FDate = adjust.FInDate And m.FNo = adjust.FInNo
And d.FStockID = adjust.FStockID And d.FPrice = adjust.FOldPrice
)
WHERE adjust.FBillDetailID is NULL;