PL/SQL procedure to compare rows in two tables

Hello there,

I need to write a procedure to compare the results in two tables. Lets call them table1 and table 2.

The field I am trying to compare is called prod_id with holds integer.

For everytime the value in 'prod_id' in  table 1 matches a value in 'prod_id' in table 2, I want to set another field in table 1 called 'match (boolean) to true elseif no match false.

So for example:

      Table 1              Table 2
prod_id  match         prod_id
1            FALSE             3
2            FALSE             7
3            TRUE              8
4            FALSE             9

I guess I need two cursors and then run some sort of loop to compare the first  row of Table 1 to every row of Table 2. Then the second row of Table 1 to every row of Table 2 and so on ?

Please could someone outline a procedure as this is driving me mad.

Thanks

Ren

BeamAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

ToddBarryCommented:
You certainly don't need PL/SQL for this - straight SQL can do this very easily.

sql>select * from table1;

  PROD_ID MATCH
--------- -----
        1
        2
        3
        4

4 rows selected.

sql>select * from table2;

  PROD_ID
---------
        3
        7
        8
        9

4 rows selected.

sql>update table1
  2     set match = nvl((select 'TRUE'
  3                        from table2
  4                       where table2.prod_id = table1.prod_id), 'FALSE');

4 rows updated.

sql>select * from table1;

  PROD_ID MATCH
--------- -----
        1 FALSE
        2 FALSE
        3 TRUE
        4 FALSE

4 rows selected.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
slightwv (䄆 Netminder) Commented:
Why PL/SQL?

I made the example set the match column to Y or N, but it should show the point:

drop table tab1;

create table tab1 (
prod_id char(1),
match char(1) default 'N'
)
/

insert into tab1(prod_id) values('1');
insert into tab1(prod_id) values('2');
insert into tab1(prod_id) values('3');
insert into tab1(prod_id) values('4');
commit;

drop table tab2;

create table tab2 (
prod_id char(1)
)
/

insert into tab2(prod_id) values('3');
insert into tab2(prod_id) values('7');
insert into tab2(prod_id) values('8');
insert into tab2(prod_id) values('9');
commit;


select * from tab1;

update tab1 t1 set match='Y' where exists (select 'x' from tab2 where prod_id=t1.prod_id)
/
commit;

select * from tab1;
0
BeamAuthor Commented:
Thanks for the quick reply.

Thats is just what I was looking for.

Thanks again

0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.