• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 783
  • Last Modified:

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

0
Beam
Asked:
Beam
2 Solutions
 
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
 
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

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now