根据其他两个表更新第三个表列

Question

我有3个表，结构如下：

 ______________
| ContractInfo |
|--------------|
| ContractId   |
| QuotationId  |
| SupplierId   |
|______________|

 ____________
| Quotation  |
|------------|
| Id         |
| ContractId |
|____________|

 ___________________________________
| QuotationInfo                     |
|-----------------------------------|
| QuotationId (References Quotation)|
| SupplierId                        |
|___________________________________|

我希望将SupplierId的值从ContractInfo复制到QuotationInfo的SupplierId列，如果QuotationId的QuotationInfo等于Quotation的Id，并且ContractId的ContractInfo等于<代码>D11的<代码>D10。

例如,

假设ContractInfo有以下数据：

ContractId  QuotationId SupplierId  
C-1         Q-1         S-1
C-2         Q-2         S-2

假设Quotation有以下数据：

Id   ContractId
Q-1  C-1
Q-2  C-2

假设QuotationInfo有以下数据：

QuotationId SupplierId
Q-1         NULL
Q-2         NULL

在update查询之后，QuotationInfo SupplierId列应按如下方式填充：

QuotationId SupplierId
Q-1         S-1
Q-2         S-2

我真的不明白我如何做到这一点。寻求帮助来实现我所描述的目标。如果不是完整的解决方案，一些提示也会很有帮助。

Answer 1

尝试使用合并

merge into QuotationInfo qf
using
(
select cf.SupplierId,cf.QuotationId  from 
ContractInfo cf 
inner join
Quotation Q 
on
cf.ContractId=Q.contractid
and 
cf.QuotationId  =q.id                   
)t 
on
(t.QuotationId=qf.QuotationId )
when matched then
update set qf.SupplierId=t.SupplierId   

Answer 2

假设QuotationId对于ContractInfo是唯一的，您可以使用子查询将SupplierId从ContractInfo复制到QuotationInfo

update QuotationInfo t1
set SupplierId = (select SupplierId
    from ContractInfo t2
    where t2.QuotationId = t1.QuotationId)

Answer 3

UPDATE QuotationInfo qi
SET qi.SupplierId = ci.SupplierId
FROM QuotationInfo qi
INNER JOIN Quotation q ON q.Id = qi.QuotationId
INNER JOIN ContractInfo ci ON ci.ContractId = q.ContractId