asked on

How to update after matching between two columns in two tables by using (IN)?

Hi Limbeck,
Regarding my previous question for (how to match between two columns in the same row), you had answered me perfectly... now I have another column in the first table and I want to update it with same value from the second table when the two columns are matched...
I had tried two procedures (A) and (B) but unfortunately none of them is working, please your solution regarding this issue...

Procedure (A) shows the following (SINGLE SUB_QUERY MORE THAN ONE)

Procedure (B) do not make any update.

(A)
CREATE OR REPLACE procedure ITC.msc_transactionTEMP is
 
begin
 
 update MSC_INCOMING_intermediate set MANAGEMENT=(select MSC_NATIONAL_INC_CODE.MANAGEMENT 
 
 From MSC_INCOMING_intermediate LEFT OUTER JOIN MSC_NATIONAL_INC_CODE
 ON (MSC_INCOMING_intermediate.Calling_dest = MSC_NATIONAL_INC_CODE.code_bef));
        
commit;
 
end msc_transactionTEMP;
 
 
 
 
(B)
 
CREATE OR REPLACE procedure ITC.msc_transactionTEMP is
 
 
b1_counter number(14);
begin
 
b1_counter := 1;
while b1_counter <=9  loop
 update MSC_INCOMING_intermediate set MANAGEMENT=(select MANAGEMENT from MSC_NATIONAL_INC_CODE)
  where SUBSTR(CALLING_DEST,1,b1_counter) IN (select code_bef from MSC_NATIONAL_INC_CODE where CALLING_LENGTH=CODE_LENGTH);
 
 b1_counter := b1_counter + 1;
 
 
end loop;
commit;
 
end msc_transactionTEMP;

Open in new window

ASKER CERTIFIED SOLUTION

Limbeck

membership

Create a free account to see this answer

Signing up is free and takes 30 seconds. No credit card required.

See answer

shwanhamawandi

ASKER

1) I will test the solution above and reply you...

2) Regarding my requirement which I agree is not clear enough, kindly find it as below:

TABLE 1 has the following columns: A, B, C, D, E, F, G, H
TABLE 2 has the following columns: L, M, N, P, R
update H with (M value in table 2) when (A in table 1) = (L in table 2)

3) Before the test, could you clarify please what do you mean by number 1 in your SQL syntax:
(select 1 From MSC_NATIONAL_INC_CODE where
MSC_INCOMING_intermediate.Calling_dest = MSC_NATIONAL_INC_CODE.code_bef);

Many thanks for your quick response

Limbeck

hm i think the query i gave you comes close to what you want :) where exists (select 1...) makes sure that only rows wich match gets updated; if you leave it those rows will be updated with a value= null.

let me know after testing and i will get back to you,

Ed.

shwanhamawandi

ASKER

Hi Limbeck,
The syntax below works perfectly after I had removed the following (;) which you had put before (where exists)

Many Thanks for your support...

update MSC_INCOMING_intermediate set MANAGEMENT=(select MSC_NATIONAL_INC_CODE.MANAGEMENT 
From MSC_NATIONAL_INC_CODE where
MSC_INCOMING_intermediate.Calling_dest = MSC_NATIONAL_INC_CODE.code_bef)
where exists
(select 1 
From MSC_NATIONAL_INC_CODE where
MSC_INCOMING_intermediate.Calling_dest = MSC_NATIONAL_INC_CODE.code_bef);

Open in new window

shwanhamawandi

ASKER

You have a wonderful ability for analyzing the questions...
THANK YOU...

Limbeck

glad to be of help :)

Ed.