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

[LittleNick]

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:

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.

 

[simian336]

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

 

[LittleNick]

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.