CODEWITHSUNDEEP
sqloracle-database
DanH
DanH

Reputation: 61

Updating a table in Oracle based on data from another table and 3 keys

All,

Here is my two tables:

Table customer

First_Name | Last_Name | Purchases| Age
  John        Rambo         10       55
  George      Washington    17       99
  Eli         Manning       7        35

Table client

First_Name | Last_Name | Purchases| Client_Id | Date_Added
  John        Rambo         10       4          12-mar-2016
  George      Washington    17       6          20-jan-2016
  Eli         Manning       7        3          10-dec-2016
  Jerry       Seinfeld      4        3          19-mar-2016

Desired result

Table customer:

    First_Name | Last_Name | Purchases| Age | Client_Id | Date_Added
     John        Rambo         10       55      4          12-mar-2016
     George      Washington    17       99      6          20-jan-2016
     Eli         Manning       7        35      3          10-dec-2016

I want to add Client_Id and Date_Added to customer. Lets say I added those columns and now have to devise the update statement. The conditions are if First_Name or Last_Name or Purchases match in both tables, populate customer. Lets just say for arguments sake these fields will always be unique so we just need one to match. How would you do this in oracle? Mind you this would be done for a couple hundred thousand records.

Upvotes: 2

Views: 41

Answers (1)

Aleksej
Aleksej

Reputation: 22969

With all your assumptions, saying that you already added the needed columns, you may need a MERGE.

setup:

create table sustomer(First_Name , Last_Name , Purchases, Age) as (
 select 'John'   , 'Rambo'      , 10 , 55 from dual union all
 select 'George' , 'Washington' , 17 , 99 from dual union all
 select 'Eli'    , 'Manning'    ,  7 , 35 from dual
);
create Table client(First_Name , Last_Name ,Purchases,Client_Id ,Date_Added) as (
 select 'John'   ,'Rambo'      , 10 , 4 , to_date('12-03-2016','dd-mm-yyyy') from dual union all
 select 'George' ,'Washington' , 17 , 6 , to_date('20-01-2016','dd-mm-yyyy') from dual union all
 select 'Eli'    ,'Manning'    ,  7 , 3 , to_date('10-12-2016','dd-mm-yyyy') from dual union all
 select 'Jerry'  ,'Seinfeld'   ,  4 , 3 , to_date('19-03-2016','dd-mm-yyyy') from dual 
);
alter table customer add Date_Added date;
alter table customer add Client_Id number;

MERGE:

merge into customer cu
using ( select * from client) cl
on ( cl.First_Name = cu.First_Name OR
     cl.Last_Name  = cu.Last_Name  OR
     cl.Purchases  = cu.Purchases
   )
when matched then
    update set Date_Added = cl.Date_Added,
               Client_Id  = cl.Client_Id

This gives, on your data:

FIRST_ LAST_NAME   PURCHASES        AGE  CLIENT_ID DATE_ADDE
------ ---------- ---------- ---------- ---------- ---------
John   Rambo              10         55          4 12-MAR-16
George Washington         17         99          6 20-GEN-16
Eli    Manning             7         35          3 10-DEC-16

Upvotes: 1

Related Questions