Problems with Update Statement

Problems with Update Statement

Post by mma.. » Sun, 31 Dec 1899 09:00:00



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

Sent via Deja.com http://www.deja.com/
Share what you know. Learn what you don't.

 
 
 

Problems with Update Statement

Post by Yar » Sun, 31 Dec 1899 09:00:00



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

 
 
 

Problems with Update Statement

Post by Michel Cado » Sun, 31 Dec 1899 09:00:00


Try this one:

update account_bal ab
set ab.amt_cur_bal =
  (select ab.amt_cur_bal - nvl(fa.amt_invalid_txn,0) from fix_acct fa
   where ab.acct_nbr = fa.acct_nbr (+));


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

>Sent via Deja.com http://www.deja.com/
>Share what you know. Learn what you don't.