How to compare two tables in best possible way

Question

There is data in two tables with same number of comuns(No primary key)-table a for active records and table b for inactive records of a particular firm. Status 'A' is for active and 'I' for inactive

Both of them have same number of columns.How can I compare the data in table a and table b such that if a record exists in b and not a then the status column in A has to be set to 'I' otherwise 'A'

Eg :-

table A

COL1 COL 2 COL3         STATUS
1    one   this is one   A
2    TO    THIS IS TO    I


Table B

Col1 col2  Col 3        status
3    THREE 33             A
4    for   this is for    A

now in table B col1 with 1 does not exist though it exists in table A with status A. Thus after comparing the Table B and Table A we will

 update table a 
set status ='I'
where col1 =1;

But i am not getting how to compare these two tables ? Kindly help?

http://stackoverflow.com/questions/5557991/minus-vs-except-difference-in-oracle-sql-server — realnumber3012, Jul 21 '14 at 08:14

score 1 · Answer 1 · answered Jul 21 '14 at 11:39

I'm confused by table a and table b, how can you set the status in table a to 'A' if there is no record in a?

In any case, I'd use MERGE for changing the status column in a table depending on values in another table:

MERGE INTO table_a 
USING table_b
   ON (table_a.col1 = table_b.col1 /* add other columns */)
 WHEN MATCHED THEN 
      UPDATE SET status = 'I' 
       WHERE status <> 'I';

This set the status in table_a if it is found in table_b. You have to specify the columns you use to identify identical columns where it says /* add other columns */. And please watch out for NULL values...

score 0 · Answer 2 · answered Jul 21 '14 at 08:24

0

To get records that in B and not in A

  select Col1,
         Col2
    from B 

  minus -- <- Oracle/PL SQL specific; other dialects use 'except'

  select Col1,
         Col2
    from A

To update A you can do something like that:

  update A
     set status = case
           when (Col1, Col2) in (
             select B1.Col1,
                    B1.Col2
               from B B1
              minus
             select A1.Col1,
                    A1.Col2
               from A A1) then
             'I'
           else 
             'A'
         end

answered Jul 21 '14 at 08:24

Dmitry Bychenko

180,369
20
160
215

1

Hi @dmitry i think i should use -(arithemitic) rather than minus....... Minus is not giving the desired result – user3809240 Jul 21 '14 at 08:43
No. @Dimitry Bychenko is right. `Minus` here means "take all records that are in first select and are not in the second select". You can't use arithmetic operator minus (-) unless you have only numeric columns to compare and you check if their difference is 0. If you get unexpected result try to exclude (from both selects) columns that might be different, like time-stamps. – B0Andrew Jul 21 '14 at 09:54

How to compare two tables in best possible way

2 Answers2