Actually, as you can see, there are 3 steps to "my solution", which I suspect is not necessary, but I haven't yet found a way to set "on_order" to zero if there's (now) nothing in winsords
On Sat, Jan 10, 2015 at 11:26 AM, Sytze de Boer <sytze.k...@gmail.com> wrote: > Tracey, you're correct. > I was trying to avoid the dreaded LOCK situation > > This works beuatifully/quick > > UPDATE winstoks SET on_order=0 > > SELECT SUM(qty) as qty, stockcode FROM winsords WHERE !EMPTY(stockcode) > GROUP BY stockcode INTO CURSOR tmp readwrite > UPDATE winstoks ; > SET winstoks.on_order=tmp.qty; > from tmp ; > WHERE winstoks.code=tmp.stockcode > > Is there a way to avoid the file locks? > UPDATE winstoks SET on_order=0 if !locked > (Yes, I know, its a silly question) > > > > > On Sat, Jan 10, 2015 at 11:14 AM, Tracy Pearson <tr...@powerchurch.com> > wrote: > >> Darren wrote on 2015-01-09: >> > UPDATE stocks SET timesheets = 0 >> > UPDATE stocks SET timesheets = tsheets.qty FROM tsheets WHERE >> stocks.stCode >> > = tsheets.stCode >> > >> > -----Original Message----- From: ProfoxTech >> > [mailto:profoxtech-boun...@leafe.com] On Behalf Of Sytze de Boer Sent: >> > Saturday, 10 January 2015 8:43 AM To: profoxt...@leafe.com Subject: >> > Help with update >> > >> > Friends >> > I know this code stinks and I'm sure there's a much better way with >> UPDATE, >> > but I can't figure it out. >> > >> > sele stocks >> > repla all timesheets with 0 >> > >> > sele tsheets >> > go top >> > scan >> > sele stocks >> > seek tsheets.stcode >> > replace timesheets with timesheets+tsheets.qty >> > sele tsheets >> > endscan >> > >> > Can anyone help with this ? >> > >> > -- >> > Kind regards, >> > Sytze de Boer >> > >> >> Darren, >> >> If Memory serves, the Update command can lock the table header during the >> process. >> If there are multiple tsheets.stcode and your are combining (summing them) >> you may need to use a subquery instead of the table directly in the >> suggested Update command. >> >> Untested: >> >> UPDATE stocks SET timesheets = tsheetsum.qty ; >> FROM (select stcode, sum(qty) as qty from tsheets group by 1) as >> tsheetsum ; >> WHERE stocks.stCode = tsheetsum.stCode >> >> Tracy Pearson >> PowerChurch Software >> >> [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/CAG1nNy8a3bt78KKbEopy2sSaqffFkArv7R+sMRL+bQU=f_+...@mail.gmail.com ** 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.
