CODEWITHSUNDEEP
sqloracle-database
Aditya Shrivastava
Aditya Shrivastava

Reputation: 329

Compare two tables and select the record that is no in table 2

I want to write a select query that will compare the two table. By comparing it should tell that what record are need to be updated, inserted or deleted from table 2.

Contents of the tables:

Table 1
----- ---------
 id    name
----- ---------
 1     shubham
 2     ravi
 3     aman
 4     vijay


Table 2
----- ---------
 id    name
----- ---------
 1     shubham
 2     ravi
 3     aman
 4     vijay

now both the table has same data now when data is changed in table1 like

Table 1
----- ---------
 id    name
----- ---------
 1     shubham
 2     harish
 3     aman                        
 5     saurabh

Now here in table1 name of id 2 is updated and id 4 is deleted also id is inserted. I want my query to select all these records from table 1 also tell whether which operation is to be done. I dont want to use set operator also. please help

Upvotes: 0

Views: 112

Answers (3)

Ponder Stibbons
Ponder Stibbons

Reputation: 14858

I would do it like here:

select coalesce(t1.id, t2.id) id, t1.name new_name, t2.name old_name,
    case when t1.id is null then 'row deleted'
         when t2.id is null then 'row inserted'
         when t1.name <> t2.name  
           or t1.name is null and t2.name is not null
           or t1.name is not null and t2.name is null
         then 'data changed'
    end change
  from t1 full join t2 on t1.id = t2.id order by id

SQLFiddle

And by the way- what's so hard in formatting data in your questions? Just add 4 spaces before something and this will be indicated as code. Or select some part and press [Ctrl-K]. Everything is rendered as you write it.

Upvotes: 0

Sentinel
Sentinel

Reputation: 6449

No set operators involved in this solution, but they are probably easier to use when there are more than just a couple of columns to compare:

select coalesce(s.id, d.id) id
     , coalesce(s.name, d.name) name
     , case when s.id is null then 'D'
            when d.id is null then 'C'
            when s.name != d.name then 'U'
       end CUD
  from table1 s
  full join table2 d
    on s.id = d.id
 where s.id is null
    or d.id is null
    or s.name != d.name

The CUD column just indicates the operation to carry out Create, Update, or Delete. The more flexible set based solution would be something like this:

select 'CU' op, s.* from table1 s
minus
select 'CU' op, d.* from table2 d
union
select 'D' op, d.* from table2 d where d.id not in (select s.id from table1 s)

In this case you don't know if the table1 records left after the minus operation are new or changed so the op is either Create or Update, but you still definitively know the Delete operations.

Either one of these queries could be used in the USING clause of a MERGE statement to update table2 to match table1

Upvotes: 1

Aditya Shrivastava
Aditya Shrivastava

Reputation: 329

SELECT 
TEST1.NAME,TEST1.CLASS,test2.name,test2.class,
case  when test1.name 
is null then 'deleted'  
when UPPER(test1.name)!=UPPER(test2.name) then 'insert'
when test2.name is null then 'updated' ELSE 'no change' end as flag
FROM    TEST1
full outer join TEST2
  ON  
 test2.class=test1.class;

Upvotes: 0

Related Questions