Question regarding Oracle UPDATE statement

[daviddmw]

Hi expert,

Really appreciate your advise how to make an update to some columns in a table based on the values from other columns in the same table.

For example, TABLE_1 has columns
METER_ID, DTM, USAGE, DP_ID, USAGE_BY_DP.
00001 20070101 1000 null null
00002 20070101 1500 null null
00003 20070101 800 null null
00004 20070101 1600 null null

TABLE_2 has
METER_ID, DP_ID, STATUS columns.
00001 10001 SUM
00002 10001 SUM
00003 10002 NONE
00004 10003 NONE

Pls note in TABLE_2, DP_ID and METER_ID has one-to-one relationship if STATUS = 'NONE'; otherwise, one DP_ID has multiple METER_IDs.

I need the following result in TABLE_1:
00001 20070101 1000 10001 2500
00002 20070101 1500 10001 2500
00003 20070101 800 10002 800
00004 20070101 1600 10003 1600

Below is a SQL I create but seems it was tripped in a dead loop.

update TABLE_1 a
set (DP_ID, USAGE_BY_DP) =
(select b.DP_ID, c.USAGE
from TABLE_2 b, TABLE_1 c
where b.METER_ID = c.METER_ID
and c.METER_ID = a.METER_ID
and b.STATUS = 'NONE')

Pls help!

Thanks in advance.

 

[ed10k]

Hi daviddmw,

a complete test-case with all "create table ..." and "insert into ..." as a sqlplus script would be very helpful!

 

[William Robertson]

How about this:

UPDATE table_1 a
SET    ( dp_id, usage_by_dp ) =
       ( SELECT b.dp_id
              , b.c.usage
         FROM   table_2 b
         WHERE  b.meter_id = a.meter_id
         AND    b.status = 'NONE' );