You must include the UPDATEd table in the list of tables in the FROM clause.
Regards, Stephen Markson ForenSys The Forensic Systems Group www.ForenSys.ca 416 482 2140 > -----Original Message----- > From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Emmitt Dove > Sent: June 9, 2004 8:08 AM > To: RBASE-L Mailing List > Subject: [RBASE-L] - Re: Update -- NOT > > Jim, > > You do not include lycopy03 anywhere in your WHERE clause, so there is no > link between lycopy03 and the two referenced tables. Where is any given > row on lycopy03 to get the value for the update? > > If lycopy03 only has one row, why not SELECT the value into a variable then > UPDATE? > > >Further Definition of problem.. > > > >The View is being created alright. It's the update statement that gives > >me the error that the Syntax is incorrect for the command. > > > >Jim > > > >Jim Limburg wrote: > > > >>Could someone fill me in on what I'm doing wrong here.. > >> > >>DROP VIEW invsmrize > >>CREATE VIEW invsmrize (category, ctotals ) + > >>AS SELECT category, SUM(cy_unit_cost * quantity) FROM tmdinv + > >>GROUP BY category > >> > >>UPDATE lycopy03 SET totalamt = (T2.ctotals) FROM tmdinv T1, invsmrize T2 + > >>WHERE T1.category = T2.category > >> > >>I am creating the view above to gather some totals, and then I just > >>want to update a column in a table with the results. The lycopy03 table > >>has just two columns Category (Text 15) and totalamt which is currency. > >>The tmdinv table has several columns but the Category is Text 15, the > >>cy_unit_cost is double and the quantity is real. In my view I get some > >>ugly results, and I'm wondering if this is the culprit here. > >> > >>Any help appreciated. > >> > >>Jim Limburg > >> > > Emmitt Dove > Manager, DairyPak Business Systems > Blue Ridge Paper Products, Inc. > 40 Lindeman Drive > Trumbull, CT 06611 > (203) 673-2231 > [EMAIL PROTECTED] > [EMAIL PROTECTED]
