All , i have two tables as shown below and i need to replace values in table1 with the values in table2 based on column2 which is a primary key. Whenever the column 3 value changes in table2 the corresponding value should be removed from table1.col1
Create Script:
CREATE TABLE TABLE1 AS
SELECT 'A,B,C' COL1,1 COL2,'Y' COL3 FROM DUAL
UNION ALL
SELECT 'B,F,R' COL1,2 COL2,'Y' COL3 FROM DUAL
UNION ALL
SELECT 'A,C,E' COL1,3 COL2,'Y' COL3 FROM DUAL;
CREATE TABLE TABLE2 AS
SELECT 'A' COL1,1 COL2,'N' COL3 FROM DUAL
UNION ALL
SELECT 'B' COL1,2 COL2,'N' COL3 FROM DUAL
UNION ALL
SELECT 'R' COL1,2 COL2,'N' COL3 FROM DUAL
UNION ALL
SELECT 'C' COL1,3 COL2,'N' COL3 FROM DUAL;
Table1
Col1 Col2 Col3
-----------------------------
A,B,C 1 Y
B,F,R 2 Y
A,C,E 3 Y
Table2
Col1 Col2 Col3
-----------------------------
A 1 N
B 2 N
R 2 N
C 3 N
After the update statement the data in my Table1 should look like below.
Table1
Col1 Col2 Col3
-----------------------------
B,C 1 Y
F 2 Y
A,E 3 Y