Nested Update sql query ..

[MaheshRathi]

Hi,
I have a doubt wrt PL/SQL - nested update query ..
My requirement is to update one column value based on some conditions i.e. based on the values in two different tables.

Here is a select query for reference .. which gives the relation -
SELECT A.BILLING_ACCOUNT_NUMBER, A.ACCOUNT_ID, B.ORDER_ID,
B.BILLING_ACCOUNT_NUMBER, B.CLASS_OF_SERVICE, B.ACCOUNT_PROVIDER_ID,
C.OWNER_NAME, C.subgroup_type_id
FROM
SDA_POF.ACCOUNTCOMMON A,
SDA_POF.POF_ACCOUNT B,
SDA_POF.DSL_DETAIL_INFO C
WHERE
C.ORDER_ID = B.ORDER_ID AND
C.SUBGROUP_TYPE_ID = 1510 AND
A.BILLING_ACCOUNT_NUMBER = B.BILLING_ACCOUNT_NUMBER AND
A.ACCOUNT_ID
IN( 180390, 11805635, 11719914, 36604, 11718956,
11720557, 11720171, 11719756, 11720785, 11715821,
11716771, 11717740, 36440, 7729116, 36174, 11718041,
11720462);

Can you please help me in resolving this? I have tried one nested update statement .. not sure if this is ok or not ..

UPDATE SDA_POF.POF_ACCOUNT B SET B.ACCOUNT_PROVIDER_ID = 657
WHERE B.BILLING_ACCOUNT_NUMBER
IN (
( SELECT BILLING_ACCOUNT_NUMBER FROM SDA_POF.ACCOUNTCOMMON
WHERE ACCOUNT_ID
IN ( 11805635, 11719914, 36604, 11718956,
11720557, 11720171, 11719756, 11720785, 11715821,
11716771, 11717740, 36440, 7729116, 36174, 11718041,
11720462)
) )
AND B.ORDER_ID
IN (
SELECT ORDER_ID FROM SDA_POF.DSL_DETAIL_INFO
WHERE SUBGROUP_TYPE_ID = 1510
);

regards,
Mahesh

 

[SantaMufasa]

Mahesh,

There should be no reason (syntactically) that your code won't work. What is the behaviour you are seeing when you test your code? What advice do you need from us?

Dave
Sandy, Utah, USA @17:08 GMT, 10:08 Mountain Time

 

[MaheshRathi]

Hi,

Can I write an update statement to update values in two columns of two different table? For e.g. I want to update col A of Table A and col B of Table B in single update statement .. can it be possible?

thanks in advance,
Mahesh

 

[sem]

Is this one-to-one relationship? I mean, does Oracle know about it?

Regards, Dima

 

[MaheshRathi]

Nope .. I don't think it is one-to-one. I have multiple records in both tables.

 

[sem]

So which ones should Oracle update??? The answer is probably NO. Read about key-preserved tables.

Regards, Dima