Update table using multiple tables

[LittleNick]

Hello everyone,
Let say I have these several tables, tblCustomerTransaction, tblCustomerShip, tblItemsType, tblItemsCost as follow:

tblCustomerTransaction

ID Item Orderdate Unit UnitCost
02 Tool 01/01/10 10
02 ToolKit 01/01/10 10
02 Electro 01/01/10 5
05 Games 01/01/10 2
05 Books 01/01/10 5
08 ToolKit 01/01/10 20
08 DVD 01/01/10 1
08 Tool 01/01/10 20
09 Tool 01/01/10 6

tblCustomerShip

ID TobeShipped Date
02 Yes 01/01/10
04 No 08/31/09
05 Yes 01/01/10
06 No 01/01/10
08 Yes 01/01/10
09 Yes 01/01/10

tblItemsOrdered

ID ItemID
02 A1
04 H3
05 C2
08 D5
09 R8

tblItemsCost

ItemID Item Cost
A1 Books $2.00
A1 Tools $3.00
A1 ToolKit $4.00
H3 DVD $5.00
H3 Books $6.00
C2 Games $7.00
C2 Books $8.00
D5 DVD $9.00
D5 Tools $10.00
D5 ToolKit $11.00
R8 Books $12.00
R8 DVD $13.00

Now I want to run an update to update tblCustomerTransaction.UnitCost of the Tool to the Cost from tblItmesCost only to the Customer (ID) who has both Tool and ToolKit (together) in the order and TobeShipped is ‘Yes’ and the date tblCustomerTransaction.OrderDate = tblCustomerShip.Date. I hope this is not too confusing, but the result I would like to obtain is after running the update, the column UnitCost in tblCustomerTransaction shoul look like below

tblCustomerTransaction

ID Item Orderdate Unit UnitCost
02 Tool 01/01/10 10 $3.00
02 ToolKit 01/01/10 10 $4.00
02 Electro 01/01/10 5
05 Games 01/01/10 2
05 Books 01/01/10 5
08 ToolKit 01/01/10 20 $11.00
08 DVD 01/01/10 1
08 Tool 01/01/10 20 $10.00
09 Tool 01/01/10 6

Appreciate your helps.

 

[djj55]

Write a select statement to show what you want then use it in the update statement.

select a.id, b.id
from abc a 
inner join bcd b 
on a.id= b.id
WHERE blah, blah, blah

-- would then be 
update a
set cost = b.cost
from abc a 
inner join bcd b 
on a.id= b.id
WHERE blah, blah, blah

Otherwords if you can create a select you can use the logic in your update.

djj
The Lord is my shepherd (Psalm 23) - I need someone to lead me!

 

[LittleNick]

Thanks djj for your advice. I combined some tables by creating view first then it became a lot easier.