The complication is you have to do a group by to get the counts. If someone (probably Bill Downall) can do this with one statement, cudos to them, but this is the way I would do it. It is easy to read an maintain.
Drop view tempview Create temp view tempview (LocName,LocCount) AS SELECT WorkLocation, count(*) from WorkOrder GROUP By WorkLocation Update location set loccount = t1.loccount from tempview t1, location t2 where t1.LocName = T2.LocName You put the view first in the table order for performance. Dennis McGrath -----Original Message----- From: [email protected] [mailto:[EMAIL PROTECTED] On Behalf Of javier valencia Sent: Friday, January 18, 2008 11:12 AM To: RBASE-L Mailing List Subject: [RBASE-L] - RE: Brain freeze... I would have thought that this is a pretty straight forward update...right... I have tried different variation of UDTATE....COUNT(), SELECT(COUNT()) and so on an I have not been able to find the right syntax...your approach seems like a good workaround. Javier, Javier Valencia, PE Sr. Project Manager Universal Asset Management, L.L.C. 801 Westchester Ave. Harrisonville, MO 64701 Phone: 816-887-4011 Fax: 816-887-1960 Cell: 913-915-3137 -----Original Message----- From: [email protected] [mailto:[EMAIL PROTECTED] On Behalf Of Emmitt Dove Sent: Friday, January 18, 2008 10:59 AM To: RBASE-L Mailing List Subject: [RBASE-L] - RE: Brain freeze... Where's David Blocker when you need him? |-) I've never been able to fully wrap my head around some of the more convoluted updates, so what I would do is this: - create a temp view based on WorkOrder that selects the location and counts the work orders group by location - update table location from the view Emmitt Dove Manager, DairyPak Business Systems Blue Ridge Paper Products, Inc. [EMAIL PROTECTED] [EMAIL PROTECTED] (203) 643-8022 -----Original Message----- From: [email protected] [mailto:[EMAIL PROTECTED] On Behalf Of javier valencia Sent: Friday, January 18, 2008 11:45 AM To: RBASE-L Mailing List Subject: [RBASE-L] - Brain freeze... I am having one of those days when my brain is just not quite working; probably anticipation for tomorrows KU-MU BBall game...Go Jayhawks... I have a table that has unique locations (LocName) and a second table that has work orders with a location column (WorkLocation) For each location in the first table I want to upgrade the count with the number of occurrences in the second table. Table Location LocName LocCount ================== LocA 2 LocB 3 LocC 0 Table WorkOrder WoNo WorkLocation ================== 1 LocA 2 LocB 3 LocB 4 LocA 5 LocB I need to update table Location with the count of values in table WorkOrder. I believe there should be an UPDATE statement that does this globally, but somehow, anything I have tried either gives me a syntax error or does not work...Any ideas??? TIA. Javier, Javier Valencia, PE President Valencia Technology Group, L.L.C. 14315 S. Twilight Ln., Suite #101 Phone: 913-829-0888 Fax: 913-649-2904 Cell: 913-915-3137 ================================================ Attention: The information contained in this message and or attachments is intended only for the person or entity to which it is addressed and may contain confidential and/or privileged material. Any review, retransmission, dissemination or other use of, or taking of any action in reliance upon, this information by persons or entities other than the intended recipient is prohibited. If you received this in error, please contact the sender and delete the material from all system and destroy all copies. ======================================================
