LittleNick
Technical User
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:
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.
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.