CODEWITHSUNDEEP
sqloracle-database
user2786962
user2786962

Reputation: 469

How to make a new column based on values in new and old table in Oracle

Given below is the structure of my data

Name    Country
s       India
d       Ind
c       Afric
r       Africa
f       Ind
v       India

There are many mistakes in the country column. Below is the table containing identified mistakes

    Old Value   New Value   Source  Column
Ind India   Avox    Country
Afric   Africa  Avox    Country

I need the following table which contains the correct value of Country

Name    Country New_Column
s       India   India
d       Ind     India
c       Afric   Africa
r       Africa  Africa
f       Ind     India
v       India   India

Given below is the command I am using. This is just the snapsshot of data. My data is very big

merge into L03_a_AVOX_DATA_test n using (
    SELECT old_value , new_value
      FROM Transformation_Data_all where column_identifier='Country' and Source_identifier='Avox'
) o ON (n.Country = o.old_value)
WHEN MATCHED THEN
  n.New_Column = o.new_value;

Upvotes: 0

Views: 115

Answers (3)

Lalit Kumar B
Lalit Kumar B

Reputation: 49062

Use DECODE function to update all the incorrect column values. Or, to make it more explicit and readable, use CASE construct.

Using DECODE

DECODE(column, 'Ind', 'India', 'Afric', 'Africa')

Using CASE

CASE
   WHEN column = 'Ind'
      THEN 'India'
   WHEN column = 'Afric'
      THEN 'Africa'
END

So, your update statement would look like,

UPDATE TABLE_NAME
   SET COUNTRY_NAME = <the decode or case expression as explained above>
 WHERE COUNTRY_NAME IN(country names list);

Upvotes: 1

APC
APC

Reputation: 146179

Your "question" doesn't actually feature a question as such. Perhaps you meant to ask was "why am I getting a compilation error when I try to run this MERGE statement?" If so, the answer is you need to include the UPDATE key word in the MATCHED clause.

merge into L03_a_AVOX_DATA_test n 
using (
    SELECT old_value , new_value
      FROM Transformation_Data_all 
      where column_identifier='Country'
    and Source_identifier='Avox'
   ) o 
   ON (n.Country = o.old_value)
WHEN MATCHED THEN
  UPDATE 
  SET  n.New_Column = o.new_value;

If you need to populate the NEW_COLUMN for all rows, as your result set suggests you do, then you can do that with a simple update after running the merge. 

update 03_a_AVOX_DATA_test n
set n.New_Column = n.Country 
where n.New_Column is null;

Upvotes: 0

user330315
user330315

Reputation:

The "simple" version: use two updates:

update the_table
   set country = 'India'
where country = 'Ind';

update the_table
   set country = 'Africa'
where country = 'Afric;

A bit more complicated using a single statement

update the_table
   set country = case 
                   when country = 'Afric' then 'Africa'
                   when country = 'Ind' then 'India'
                 end
where country in ('Afric', 'Ind');

Upvotes: 0

Related Questions