Update a field of table 2

[LittleNick]

Hi everyone.

I have a table 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

I want to update the match field of Book and DVD to contain the unit of Games when games is in the group. as this

OrderID Item Unit Match
01 Book 2 3
01 DVD 4 3
01 Games 3
02 Book 5
02 DVD 6
03 DVD 7 8
03 Games 8

I am having problem to create the Where clause so that it grab the correct Games unit. Thanks for help.

 

[bborissov]

UPDATE tblOrder SET Match = Tbl1.Unit
FROM tblOrder
INNER JOIN tblOrder Tbl1
       ON tblOrder.OrderId = Tbl1.OrderId AND
          Tbl1.Item = 'Games'
WHERE tblOrder.Item IN ('Book', 'DVD')

Borislav Borissov
VFP9 SP2, SQL Server 2000,2005 & 2008.

 

[LittleNick]

Thanks bborissov for you help. The codes you have work great.

Sorry, I actually forgot a second table with a date. If I have another table as tblDate

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

And I want to update tblOrder with a date of '03/15/2010' giving the result similar to:

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

Again, I am sorry to left out the date requirement and again thanks for everyone's help.

 

[bborissov]

OK,
please provide some example data from ALL tables and the final desired result
BTW if you use TGML (this is the TAGS used here for some formatting you could make your code and data more readable.

Just click on "Process TGML" link after the message creating area (not the checkbox, just the link)


Borislav Borissov
VFP9 SP2, SQL Server 2000,2005 & 2008.

 

[LittleNick]

OK. Sorry for the confusion.
I actually have 2 tables, table tblOrder and table tblOrderDate more accurate.

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

Now if a user supplies a date for example 03/15/2010
then I want to update the table to look like this 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

Because OrderID 01, 04, 05 have the Date 03/15/2010 and because they all have Games so the Match field is updated to contain the Games unit, while 02 have the same date 03/15/2010 but does not have Games.

Does this make more sense?

Again I appreciate your time and helps.

 

[simian336]

Just continued bborissov's code...
Added a second join to get the date

Changed WHERE tblOrder.Item IN ('Book', 'DVD')
to
WHERE #tblOrder.Item <> 'Games'
So that you would not have to make changes in the future if more categories were added.


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'

Good luck

Simi

 

[LittleNick]

That works GREAT!
Thanks Simi and bborissov.