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