update and correlated subquery

[Lesio]

I am trying to run an update statement in Queryman with a subquery (listed below):
I am getting a syntax error "expected something between '(' and the 'sel' keyword".

Any ideas how to re-write it ?

update OLAPV2_DB.TO_L59_SUSP_BAL_AMT

set balance_amt =

(sel

balance_amt

from

pmdbivm_prd.coll_balance_hist

where

acct_id = OLAPV2_DB.TO_L59_SUSP_BAL_AMT.acct_id

and

(acct_id, eff_dt)

in (sel y.acct_id, min(y.eff_dt)
from pmdbivm_prd.coll_balance_hist y,
OLAPV2_DB.TO_L59_SUSP_BAL_AMT z

where

y.acct_id = z.acct_id
and z.eff_dt >= y.max_l59_susp_dt

group by 1)

and OLAPV2_DB.TO_L59_SUSP_BAL_AMT.balance_amt is null
)


;

 

[l610d42s]

Hi Buddy,
U can try the following. I have just changed the way you were updating.

update OLAPV2_DB.TO_L59_SUSP_BAL_AMT

FROM

(sel balance_amt as amt

from

pmdbivm_prd.coll_balance_hist

where

acct_id = OLAPV2_DB.TO_L59_SUSP_BAL_AMT.acct_id

and

(acct_id, eff_dt)

in (sel y.acct_id, min(y.eff_dt)
from pmdbivm_prd.coll_balance_hist y,
OLAPV2_DB.TO_L59_SUSP_BAL_AMT z

where

y.acct_id = z.acct_id
and z.eff_dt >= y.max_l59_susp_dt

group by 1)

and OLAPV2_DB.TO_L59_SUSP_BAL_AMT.balance_amt is null
) test
set balance_amt = test.amt

 

[l610d42s]

Just to add. I did not check your SELECT clause but I think if ur SELECT query is fine then the solution i have suggested should work,although I haven't tested it.

 

[Lesio]

Thanks !
The derived table, you created works OK, but whole update statement returns syntax error "expected something between the word 'OLAPV2_DB' and '.', regardless if 'set' command is on the top or the bottom.

update OLAPV2_DB.TO_L59_SUSP_BAL_AMT
set OLAPV2_DB.TO_L59_SUSP_BAL_AMT.balance_amt = test.amt
FROM
(
sel pmdbivm_prd.coll_balance_hist.balance_amt as amt
from
pmdbivm_prd.coll_balance_hist,
OLAPV2_DB.TO_L59_SUSP_BAL_AMT
where
pmdbivm_prd.coll_balance_hist.acct_id = OLAPV2_DB.TO_L59_SUSP_BAL_AMT.acct_id
and
(pmdbivm_prd.coll_balance_hist.acct_id, pmdbivm_prd.coll_balance_hist.eff_dt)
in (sel y.acct_id, min(y.eff_dt)
from pmdbivm_prd.coll_balance_hist y,
OLAPV2_DB.TO_L59_SUSP_BAL_AMT z
where
y.acct_id = z.acct_id
and y.eff_dt >= z.max_l59_susp_dt
group by 1)
and OLAPV2_DB.TO_L59_SUSP_BAL_AMT.balance_amt is null
) test

 

[Lesio]

Got the winning query - just by putting
set balance_amt = test.amt in the end ! Thanks for help.

Do you know by chance how to create table in MS Access through Queryman (SQL statement) ?