Tweet
kevin.brunk wrote: > I was asked to perform a rather simple update of one file using data > from another. I used the following statement. > > update inp35 a > set vatc35 = (select newvatc35 from updatevat3 > where burnsno = a.pnum35) > where a.cono35 = 'WA' and pnum35 in (select burnsno from pdatevat3) I've found that the "in" selection criteria can run much longer than using "exists": . . . Where a.cono35='WA' and exists (select burnsno from pdatevat3 where burnsno=a.pnum35) I've guessed that for some conditions the query engine will run the select for each and every record it tests instead of just running it once. Bill
-
-
SQL Update efficiency/syntax
Kevin Just curious, it looks like from your SQL statement, you're using the GEAC System 21 software. What type buisness are you in, and which version of System 21 do you use? We're in manufacturing and distribution. We're on version 3.5.2 SP4, and planning to upgrade to Aurora within the next year. -
SQL Update efficiency/syntax
I was asked to perform a rather simple update of one file using data from another. I used the following statement. update inp35 a set vatc35 = (select newvatc35 from updatevat3 where burnsno = a.pnum35) where a.cono35 = 'WA' and pnum35 in (select burnsno from pdatevat3) File INP35 has a record for each item (PNUM35). I'm just updating the VATC35 value of INP35 from a list of new VATCs by product number (BURNSNO). Is this the best / most efficient syntax to do this type of update?Comment
-
SQL Update efficiency/syntax
Thanks, Bill - I'll try it and compare results. Yes, Ronnie, we are a S21 user. We are a distributor/wholesaler and use (gasp!) 3.5.0b.Comment
Comment