CODEWITHSUNDEEP
sqloracle-database
Mubin
Mubin

Reputation: 4239

Update 3rd table column based on other 2 tables

I have 3 tables with below structure:

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

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

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

I would like to copy the values of SupplierId from ContractInfo to SupplierIdcolumn of QuotationInfo if QuotationId of QuotationInfo equals to Id of Quotation and ContractId of ContractInfo is equal to ContractId of Quotation.

For example,

Suppose ContractInfo has below data:

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

Suppose Quotation has below data:

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

and suppose QuotationInfo has below data:

QuotationId SupplierId
Q-1         NULL
Q-2         NULL

After the update query, the QuotationInfo SupplierId column should be populated as below:

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

Couldn't really understand how I can achieve that. Looking for help in achieving what I have described. If not the complete solution, some hints would be helpful too.

Upvotes: 2

Views: 136

Answers (3)

hamish
hamish

Reputation: 1182

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

Upvotes: 0

FuzzyTree
FuzzyTree

Reputation: 32402

Assuming QuotationId is unique to ContractInfo, you can use a subquery to copy SupplierId from ContractInfo to QuotationInfo

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

Upvotes: 1

Tharunkumar Reddy
Tharunkumar Reddy

Reputation: 2813

Try to use merge

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

Upvotes: 1

Related Questions