Maybe something like: SELECT stockcode ; FROM invtable ; GROUP BY 1 ; HAVING SUM(qty) = 0 ; INTO CURSOR w_delete NOFILTER readwrite
DELETE stockcode FROM w_delete ; WHERE stockcode.stockcode = w_delete.stockcode -----Original Message----- From: ProfoxTech [mailto:profoxtech-boun...@leafe.com] On Behalf Of Fernando D. Bozzo Sent: Wednesday, 30 July 2014 9:21 AM To: profoxt...@leafe.com Subject: Re: Help with Coding In this case you need an Update-SQL. I don't remember exact syntax when combined with a Select-SQL, but is something like this: UPDATE stocktable SET qty = ( select SUM(qty) as ttl FROM invtable where invtable.stockcode=stocktable.code ) Look at Example 3 in this link: http://msdn.microsoft.com/en-us/library/8hwekas8%28v=vs.80%29.aspx Regards.- 2014-07-30 1:15 GMT+02:00 Sytze de Boer <sytze.k...@gmail.com>: > I want to the fastest possible qty update to the stocktable with > information from another table The tables are stand-alone tables > > > > On Wed, Jul 30, 2014 at 11:10 AM, Fernando D. Bozzo > <fdbo...@gmail.com> > wrote: > > > You can use a Select-SQL as a subquery for another Select-SQL, but a > > REPLACE or DELETE commands, not being SQL, can't use subqueries, > > except > you > > use it inside a function called as part of the REPLACE/DELETE > > expression condition (but it can complicate things too much). > > > > Sometimes it's better to have more readability, even if this implies > > the use of more than 1 line of code. > > > > What are you exactly want to do? > > > > > > > > 2014-07-30 1:00 GMT+02:00 Sytze de Boer <sytze.k...@gmail.com>: > > > > > I seem to recall a similar line of code where I could something > > > like sele stocktable delete for (select sum(qty) as ttl, stockcode > > > from othertable) > > > > > > I was hoping to do this in 1 line, but as Tracy said, maybe a scan > > > will have to suffice > > > > > > scan > > > select SUM(qty) as ttl,stockcode FROM invtable where > > > stockcode=stocktable.code > > > if ttl=0 > > > sele stocktable > > > delete > > > endif > > > endscan > > > > > > > > > > > > On Wed, Jul 30, 2014 at 10:52 AM, Fernando D. Bozzo > > > <fdbo...@gmail.com > > > > > wrote: > > > > > > > Hi Sytze: > > > > > > > > You can't use _tally this way. You are assuming too much using > > > > like > > this, > > > > and this system variable is updated with every VFP command that > > operates > > > on > > > > records, as Select-SQL, REPLACE, UPDATE, and the like. > > > > In your example, _tally is updated *after* REPLACE is executed > > > > with > the > > > > number of records affected, but can be affected also with the > > Select-SQL > > > at > > > > the same time. > > > > > > > > You didn't mention what the error is, but you can't use a > > > > Select-SQL > > as a > > > > function or expression, because it is a command. You should > > > > decompose > > > this, > > > > may be using this Select-SQL inside a function, but the _tally > > behaviour > > > > can be erratic in this use case. > > > > > > > > > > > > Best regards.- > > > > > > > > > > > > > > > > 2014-07-30 0:22 GMT+02:00 Sytze de Boer <sytze.k...@gmail.com>: > > > > > > > > > Friends > > > > > Can someone point out me error here please > > > > > > > > > > select stocktable > > > > > REPLACE ALL qty WITH IIF(_tally>0,select SUM(qty) as > > > > > ttl,stockcode > > FROM > > > > > invtable where stockcode=stocktable.code,ttl,0)) > > > > > > > > > > -- > > > > > Kind regards, > > > > > Sytze de Boer > > > > > Kiss Software > > > > > > > > > > > > > > > --- StripMime Report -- processed MIME parts --- > > > > > multipart/alternative > > > > > text/plain (text body -- kept) > > > > > text/html > > > > > --- > > > > > [excessive quoting removed by server] _______________________________________________ Post Messages to: ProFox@leafe.com Subscription Maintenance: http://mail.leafe.com/mailman/listinfo/profox OT-free version of this list: http://mail.leafe.com/mailman/listinfo/profoxtech Searchable Archive: http://leafe.com/archives/search/profox This message: http://leafe.com/archives/byMID/profox/001201cfab85$7e5a7050$7b0f50f0$@ozemail.com.au ** All postings, unless explicitly stated otherwise, are the opinions of the author, and do not constitute legal or medical advice. This statement is added to the messages for those lawyers who are too stupid to see the obvious.
