Login ProductsSalesSupportDownloadsAbout |
Home » Technical Support » ElevateDB Technical Support » Support Forums » ElevateDB SQL » View Thread |
Messages 1 to 10 of 11 total |
Update Data (Set) |
Tue, Nov 19 2013 2:52 PM | Permanent Link |
Ben Sprei CustomEDP | Is there any way to update table 1 based on data in table 2 using the set
command. ie set a.basEx = b.BaseEX where a.Wage between b.Start and b.End Table 1 has 50 records and I need this query to step thru Table 1 and find the corresponding qualifying data in table 2 and update table 1 Thanks Ben |
Tue, Nov 19 2013 3:35 PM | Permanent Link |
Ben Sprei CustomEDP | I tried something like this:
UPDATE table_1 as t1 SET population = ( SELECT population FROM table_2 WHERE iso = t1.iso_code LIMIT 1 ) and I get the following error ElevateDB Error #1011 An error occurred with the query (SELECT ALL "PerEX" AS "PerEX" FROM "Common"."PRExTable" AS "b" WHERE "a"."Marstat" = "b"."TPID") (A scalar query can only return a single value) Any way around this "Ben" <arrow1432@verizon.net> wrote in message news:D53405CA-BE72-41B7-B725-DE529CC455AD@news.elevatesoft.com... > Is there any way to update table 1 based on data in table 2 using the set > command. ie > set a.basEx = b.BaseEX where a.Wage between b.Start and b.End > Table 1 has 50 records and I need this query to step thru Table 1 and find > the corresponding qualifying data in table 2 and update table 1 > > Thanks > Ben > |
Tue, Nov 19 2013 4:57 PM | Permanent Link |
Jeff Cook Aspect Systems Ltd | Ben
Perhaps using DISTINCT will convince the engine that there is only one value in the result set. Failing that MAX(population) might give you the correct result. Cheers Jeff -- Jeff Cook Aspect Systems Ltd www.aspect.co.nz "Ben" <arrow1432@verizon.net> wrote in message news:86A1D392-A7A7-4844-B9A7-85016A4A46AD@news.elevatesoft.com... >I tried something like this: > > UPDATE table_1 as t1 > SET population = ( > SELECT population > FROM table_2 > WHERE > iso = t1.iso_code > LIMIT 1 > ) > > and I get the following error > > ElevateDB Error #1011 An error occurred with the query (SELECT ALL "PerEX" > AS "PerEX" FROM "Common"."PRExTable" AS "b" WHERE "a"."Marstat" = > "b"."TPID") (A scalar query can only return a single value) > > Any way around this > > "Ben" <arrow1432@verizon.net> wrote in message > news:D53405CA-BE72-41B7-B725-DE529CC455AD@news.elevatesoft.com... >> Is there any way to update table 1 based on data in table 2 using the set >> command. ie >> set a.basEx = b.BaseEX where a.Wage between b.Start and b.End >> Table 1 has 50 records and I need this query to step thru Table 1 and >> find the corresponding qualifying data in table 2 and update table 1 >> >> Thanks >> Ben >> > > |
Wed, Nov 20 2013 4:27 AM | Permanent Link |
Roy Lambert NLH Associates Team Elevate | Ben
Minor point "I tried something like this" with an error message does not help. We need to see what actually produced the error. Major point: if the sub-query does produce multiple rows then unless they are all identical how can the engine select one to use to update with? So first thing you need to do is have a look at PRExTable and see if there are multiple values for TPID in it. If there are and there shouldn't be then you need to clean the table. If there are and there should be you need to develop a query which will return just the one you're interested in. I advise against using DISTINCT or RANGE to limit the selection to scalar unless you know with absolute certainty that it will deliver the correct result for every row in the target table. If you want more assitance please post the table structures, what you've tried and a description of what you want to achieve. Roy Lambert [Team Elevate] |
Wed, Nov 20 2013 10:45 AM | Permanent Link |
Ben Sprei CustomEDP | Thanks for your reply. You are right. I limited the result set to one and
it works. However I have several fields to update. I am displaying the code. update PRTemp a set FPerEX = ( select PerEX from Common.PRExTable b where b.TType = 'FWT' and b.Period = 'W' and b.TPID = a.Marstat ) update PRTemp a set SBaseEX = ( select PerEX from Common.PRExTable b where b.TType = 'NYS' and b.Period = 'W' and b.TPID = a.Marstat ) update PRTemp a set SPerEX = ( select PerEX from Common.PRExTable b where b.TType = 'NYS' and b.Period = 'W' and b.TPID = a.Marstat ) update PRTemp a set CPerEX = ( select PerEX from Common.PRExTable b where b.TType = 'NYC' and b.Period = 'W' and b.TPID = a.Marstat ) update PRTemp a set CBaseEX = ( select PerEX from Common.PRExTable b where b.TType = 'NYC' and b.Period = 'W' and b.TPID = a.Marstat ) Im wondering if there is anyway to combine any of the statements to avoid that many passes. Thanks Ben "Roy Lambert" <roy@lybster.me.uk> wrote in message news:ED562573-48F2-43C4-9C1D-16443BD753C6@news.elevatesoft.com... > Ben > > > Minor point "I tried something like this" with an error message does not > help. We need to see what actually produced the error. > > Major point: if the sub-query does produce multiple rows then unless they > are all identical how can the engine select one to use to update with? > > So first thing you need to do is have a look at PRExTable and see if there > are multiple values for TPID in it. If there are and there shouldn't be > then you need to clean the table. If there are and there should be you > need to develop a query which will return just the one you're interested > in. > > I advise against using DISTINCT or RANGE to limit the selection to scalar > unless you know with absolute certainty that it will deliver the correct > result for every row in the target table. > > If you want more assitance please post the table structures, what you've > tried and a description of what you want to achieve. > > > Roy Lambert [Team Elevate] > |
Wed, Nov 20 2013 12:56 PM | Permanent Link |
Roy Lambert NLH Associates Team Elevate | Ben
>Im wondering if there is anyway to combine any of the statements to avoid >that many passes. At least partially update PRTemp a set FPerEX (select PerEX from Common.PRExTable b where b.TType = 'FWT' and b.Period = 'W' and b.TPID = a.Marstat), SBaseEX (select PerEX from Common.PRExTable b where b.TType = 'NYS' and b.Period = 'W' and b.TPID = a.Marstat ) etc should work - but not guaranteed Roy Lambert [Team Elevate] |
Wed, Nov 20 2013 1:46 PM | Permanent Link |
Tim Young [Elevate Software] Elevate Software, Inc. timyoung@elevatesoft.com | Ben,
<< Im wondering if there is anyway to combine any of the statements to avoid that many passes. >> Yes, just combine them: update PRTemp a set FPerEX = ( select PerEX from Common.PRExTable b where b.TType = 'FWT' and b.Period = 'W' and b.TPID = a.Marstat ), SBaseEX = ( select PerEX from Common.PRExTable b where b.TType = 'NYS' and b.Period = 'W' and b.TPID = a.Marstat ), SPerEX = ( select PerEX from Common.PRExTable b where b.TType = 'NYS' and b.Period = 'W' and b.TPID = a.Marstat ), CPerEX = ( select PerEX from Common.PRExTable b where b.TType = 'NYC' and b.Period = 'W' and b.TPID = a.Marstat ), CBaseEX = ( select PerEX from Common.PRExTable b where b.TType = 'NYC' and b.Period = 'W' and b.TPID = a.Marstat ) Tim Young Elevate Software www.elevatesoft.com |
Wed, Nov 20 2013 4:56 PM | Permanent Link |
Ben Sprei CustomEDP | Thanx
"Tim Young [Elevate Software]" <timyoung@elevatesoft.com> wrote in message news:F09C0BC8-DD9C-4703-B9B2-97A960D18237@news.elevatesoft.com... > Ben, > > << Im wondering if there is anyway to combine any of the statements to > avoid that many passes. >> > > Yes, just combine them: > > update PRTemp a > set FPerEX = > ( > select PerEX from Common.PRExTable b > where b.TType = 'FWT' and b.Period = 'W' and b.TPID = a.Marstat > ), > SBaseEX = > ( > select PerEX from Common.PRExTable b > where b.TType = 'NYS' and b.Period = 'W' and b.TPID = a.Marstat > ), > SPerEX = > ( > select PerEX from Common.PRExTable b > where b.TType = 'NYS' and b.Period = 'W' and b.TPID = a.Marstat > ), > CPerEX = > ( > select PerEX from Common.PRExTable b > where b.TType = 'NYC' and b.Period = 'W' and b.TPID = a.Marstat > ), > CBaseEX = > ( > select PerEX from Common.PRExTable b > where b.TType = 'NYC' and b.Period = 'W' and b.TPID = a.Marstat > ) > > Tim Young > Elevate Software > www.elevatesoft.com |
Wed, Nov 20 2013 5:23 PM | Permanent Link |
Ben Sprei CustomEDP | Is there anyway to write code like this:
update PRWorkSheet set XFica = 0.00 where SalaryOT is null, set xwht = 0.00 where SalaryReg is NULL The above code as is returns an error Ben "Ben" <arrow1432@verizon.net> wrote in message news:92AA6960-67EC-455E-992D-5198CD5A11F7@news.elevatesoft.com... > Thanx > "Tim Young [Elevate Software]" <timyoung@elevatesoft.com> wrote in message > news:F09C0BC8-DD9C-4703-B9B2-97A960D18237@news.elevatesoft.com... >> Ben, >> >> << Im wondering if there is anyway to combine any of the statements to >> avoid that many passes. >> >> >> Yes, just combine them: >> >> update PRTemp a >> set FPerEX = >> ( >> select PerEX from Common.PRExTable b >> where b.TType = 'FWT' and b.Period = 'W' and b.TPID = a.Marstat >> ), >> SBaseEX = >> ( >> select PerEX from Common.PRExTable b >> where b.TType = 'NYS' and b.Period = 'W' and b.TPID = a.Marstat >> ), >> SPerEX = >> ( >> select PerEX from Common.PRExTable b >> where b.TType = 'NYS' and b.Period = 'W' and b.TPID = a.Marstat >> ), >> CPerEX = >> ( >> select PerEX from Common.PRExTable b >> where b.TType = 'NYC' and b.Period = 'W' and b.TPID = a.Marstat >> ), >> CBaseEX = >> ( >> select PerEX from Common.PRExTable b >> where b.TType = 'NYC' and b.Period = 'W' and b.TPID = a.Marstat >> ) >> >> Tim Young >> Elevate Software >> www.elevatesoft.com > > |
Wed, Nov 20 2013 5:51 PM | Permanent Link |
Jeff Cook Aspect Systems Ltd | -- Jeff Cook Aspect Systems Ltd www.aspect.co.nz "Ben" <arrow1432@verizon.net> wrote in message news:11FDF902-17F0-4A0D-972C-2BD20C9873F8@news.elevatesoft.com... > Is there anyway to write code like this: > > update PRWorkSheet > set XFica = 0.00 where SalaryOT is null, > set xwht = 0.00 where SalaryReg is NULL > > The above code as is returns an error > Ben Try UPDATE PRWorkSheet SET XFica = IFNULL(XFica THEN 0.00 ELSE XFica), xwht = IFNULL(SalaryReg THEN 0.00 ELSE xwht) Cheers Jeff |
Page 1 of 2 | Next Page » | |
Jump to Page: 1 2 |
This web page was last updated on Thursday, September 26, 2024 at 10:04 PM | Privacy PolicySite Map © 2024 Elevate Software, Inc. All Rights Reserved Questions or comments ? E-mail us at info@elevatesoft.com |