Re: Problems with Update Statement

  mmacna_at_ftg-inc.com wrote:
> I am having a problem with an update statement. Basically, I double
> posted some accounts, so I created a table called fix_acct with two
> columns,
>
> acct_nbr
> amt_invalid_txn
>
> I then have another table, account_bal, with the following relevant
> columns:
>
> acct_nbr
> amt_cur_bal
>
> This is may statement:
>
> update account_bal ab
> set ab.amt_cur_bal =
> (select ab.amt_cur_bal - fa.amt_invalid_txn from fix_acct fa
> where ab.acct_nbr = fa.acct_nbr)
>
> This works for the accounts where the account numbers match, but sets
> the amt_cur_bal to NULL for all others. I am running Oracle 8 on NT.
>
> Any thoughts?
>
> Thanks

Try this:

update account_bal ab
set ab.amt_cur_bal =
  (select ab.amt_cur_bal - fa.amt_invalid_txn from fix_acct fa    where ab.acct_nbr = fa.acct_nbr)
where ab.acct_nbr in (select acct_nbr from fix_acct)

Hope this helps.

Yar

Sent via Deja.com http://www.deja.com/
Share what you know. Learn what you don't. Received on Wed Sep 22 1999 - 11:05:11 CDT