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

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
>

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
>>
>
>

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]

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]
>

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]

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

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

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
>
>

--
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