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!

[b]Update a Field of table II[/b]

Status
Not open for further replies.

LittleNick

Technical User
Jun 26, 2009
55
US
Hello Everyone,

In an ealier post, I have 2 tables as below:

tblOrder
OrderID Item Unit Match
01 Book 2
01 DVD 4
01 Games 3
02 Book 5
02 DVD 6
03 DVD 7
03 Games 8
04 Book 9
04 Games 10
05 Book 11
05 DVD 12
05 Games 13


tblDate
OrderID Date
01 03/15/2010
02 03/15/2010
03 07/01/2010
04 03/15/2010
05 03/15/2010

and I wanted to update tblOrder.Match field of Book and DVD to contain the unit of Games when Games is in the group (of same orderid) as below:

tblOrder (updated)
OrderID Item Unit Match
01 Book 2 3
01 DVD 4 3
01 Games 3
02 Book 5
02 DVD 6
03 DVD 7
03 Games 8
04 Book 9 10
04 Games 10
05 Book 11 13
05 DVD 12 13
05 Games 13

I got the help from Simi and bborissov with the code:
Code:
UPDATE tblOrder
SET Match = Tbl1.Unit
FROM tblOrder
INNER JOIN tblOrder Tbl1       
    ON tblOrder.OrderId = Tbl1.OrderId
    AND Tbl1.Item = 'Games'
inner join tbldate
    ON tblOrder.OrderId = tbldate.OrderId
    and tbldate.date= '03/15/2010'
WHERE tblOrder.Item <> 'Games'

Now if I have a third table tblCost:

OrderID Cost
01 $50.00
02 $60.00
03 $70.00
04 $80.00
05 $90.00

if the user supply a date as '03/15/2010', I would like to update tblOrder.Cost so the result would look like:

tblOrder (updated)
OrderID Item Unit Cost
01 Book 2 $50.00
01 DVD 4 $50.00
01 Games 3
02 Book 5
02 DVD 6
03 DVD 7
03 Games 8
04 Book 9 $80.00
04 Games 10
05 Book 11 $90.00
05 DVD 12 $90.00
05 Games 13

Appreciate your helps.
 
UPDATE tblOrder
SET cost= tblCost.cost
FROM tblOrder

INNER JOIN tblOrder Tbl1
ON tblOrder.OrderId = Tbl1.OrderId
AND Tbl1.Item = 'Games'

inner join tbldate
ON tblOrder.OrderId = tbldate.OrderId
and tbldate.date= '03/15/2010'

inner join tblCost
ON tblOrder.OrderId = tblCost.OrderId

WHERE tblOrder.Item <> 'Games'


Simi
 
Yes! That works Great! Thank You Simi!

I have a process that does the calculation, but I was wondering if SQL can be programmed to update and calculate. For ex using tblOrder (updated):

tblOrder (updated) (with calculation)
OrderID Item Unit Match
01 Book 2 2
01 DVD 4 1
01 Games 3
04 Book 9 9
04 Games 10
05 Book 11 11
05 DVD 12 2
05 Games 13

What I mean with calculation is for OrderID 01, the Match is 3 (in total) then when it match Book (unit 2), it calculates 3-2 = 1. Therefore, DVD Match is can obly be 1 because that is what is left. The same is for OrderID 05 where the Match is 13, Book Match 11, 13-11 = 2, that is why DVD Match is only 2.

I have a process using C# codes to do the calculation, but want to know if this is possible for future use.

Again, Thank you very much Simi for your help.
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top