update statement

[m2001331]

Hi all,

I have a table which needs to be updated.The update condition is the records in the table may or may not change.
eg.
ACC_NO varchar2(20)
NAME varchar2(100)
LAST_AMT number
DEPOSIT_AMT number
the PK is ACC_NO.

The value of LAST_AMT and DEPOSIT_AMT may change, however not all records in the table will have a change of these values.
How can I construct a update statement to ensure that this table is kept up to date.
This table derives the values from another table containing these values.
Please help.
thanks and regards.

 

[aryeh1010]

Do you mean that the LAST_AMT and DEPOSIT_AMT fields have to match the values in another table and that you only want to update those fields when they are different from the fields that you are checking against?

Aryeh Keefe

 

[m2001331]

hi,

Yes you're right.I need to update those two fields when there is a change.

thanks

 

[aryeh1010]

try this


update table_to_be_updated a
set
(last_amt, deposit_amt) =
(
select LAST_AMT, DEPOSIT_AMT
from source_table b
where
a.acc_no = b.acc_no
)
where exists
(
select LAST_AMT, DEPOSIT_AMT
from source_table c
where
a.acc_no = c.acc_no and
(
a.last_amt <> c.last_amt or
a.deposit_amt <> c.deposit_amt
)
)


If the update is slow, it can be tuned by doing it in two steps. In the where exists sub-query just use an equi-join on last_amt, and in the second step do the same for deposit_amt


Aryeh Keefe

 

[m2001331]

Hi thanks for the script.It worked.
rgds.