Skip to Main Content
  • Questions
  • Compare for matching recrds i n two table and return true or false against each row

Breadcrumb

May 4th

Question and Answer

Connor McDonald

Thanks for the question, Vahid.

Asked: June 18, 2018 - 8:51 am UTC

Last updated: June 28, 2018 - 6:40 am UTC

Version: Oracle SQl developer 4.2

Viewed 1000+ times

You Asked

Dear Tom

I am sorry if this has been dealt before, I am a complete beginner to ORACLE SQL and will appreciate your help.

I have two tables table A and Table B.

Table A has about 60,000 rows of customer data with Customer Id as main identifier. However the Customer Ids are not unique and there may be multiple records for one customer.

table B has one Column with over 500,000 unique customer ids, these are unique ids.

What I need is help with following:

Look at customer Ids from Table A and search in Table B to see if that customer Id exists. If so then insert a yes (or True) in TableA.Customer_exists column , if not then insert No (or False) in TableA.Customer_exists. This should go through all the entries in Table A and even if I have multiple Ids in Table A then I would have multiple Y's or N's in tableA-Customer_exists column

Thank you in advance and step by step code will be appreciate it as I am just learning SQL


and Connor said...

A merge should take care of most of it

SQL> create table ta ( id int, cust int, yes_or_no varchar2(1) );

Table created.

SQL> create table tb ( cust int primary key ) ;

Table created.

SQL>
SQL> insert into ta
  2  select rownum, dbms_random.value(1,500000), null
  3  from dual
  4  connect by level <= 60000;

60000 rows created.

SQL>
SQL> insert into tb
  2  select rownum*2
  3  from dual
  4  connect by level <= 500000;

500000 rows created.

SQL>
SQL> commit;

Commit complete.

SQL>
SQL>
SQL> merge into ta
  2  using ( select * from tb ) b
  3  on ( ta.cust = b.cust )
  4  when matched then
  5   update set yes_or_no = 'Y';

30057 rows merged.

SQL>
SQL> update ta set yes_or_no = 'N' where yes_or_no is null;

29943 rows updated.

SQL>
SQL> select * from ta where rownum < 50;

        ID       CUST Y
---------- ---------- -
      2087     179958 Y
      2088     275279 N
      2089     187087 N
      2090      34816 Y
      2091     444243 N
      2092     468718 Y
      2093     325392 Y
      2094     397626 Y
      2095     253285 N
      2096      43035 N
      2097     168041 N
      2098     216580 Y
      2099      76296 Y
      2100     281676 Y
      2101     173866 Y
      2102      91198 Y
      2103     322284 Y
      2104     290060 Y
      2105     315403 N
      2106     423184 Y
      2107     386996 Y
      2108      32697 N
      2109     316326 Y
      2110     320719 N
      2111     438909 N
      2112     427618 Y
      2113      80531 N
      2114     131810 Y
      2115     104332 Y
      2116     122527 N
      2117     330385 N
      2118      61493 N
      2119      78733 N
      2120      17073 N
...
...



Is this answer out of date? If it is, please let us know via a Comment

More to Explore

PL/SQL demos

Check out more PL/SQL tutorials on our LiveSQL tool.

PL/SQL docs

PL/SQL reference manual from the Oracle documentation library