Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Update table using multiple tables

Status
Not open for further replies.

LittleNick

Technical User
Jun 26, 2009
55
US
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.
 
Write a select statement to show what you want then use it in the update statement.
Code:
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!
 
Thanks djj for your advice. I combined some tables by creating view first then it became a lot easier.

 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top