CODEWITHSUNDEEP
oracle-databaseplsql
lzc
lzc

Reputation: 1705

Comparing two tables, if rows are different, run query in Oracle

Think of my two tables have the same columns. One column is the ID, and the other one is the text. Is it possible to implement the following pseudo code in PLSQL?

Compare each row (They will have the same ID)
    If anything is different about them
        Run a couple of queries: an Update, and an Insert
    ElseIf they are the same
        Do nothing
    Else the row does not exist
        So add the row to the table compared on

Is it easy to do this using PLSQL or should I create a standalone application to do do this logic.

Upvotes: 0

Views: 7450

Answers (4)

Sylvain Leroux
Sylvain Leroux

Reputation: 51990

As your table have the same columns, by using NATURAL JOIN you can easily check if two corresponding rows are identical -- without need to update your code if a column is added to your table.

In addition, using OUTER JOIN allow you to find the rows present in one table but not in the other.

So, you can use something like that to achieve your purpose:

for rec in (
    SELECT T.ID ID1,
           U.ID ID2,
           V.EQ
           FROM T 
           FULL OUTER JOIN U ON T.ID = U.ID
           FULL OUTER JOIN (SELECT ID, 1 EQ FROM T NATURAL JOIN U) V ON U.ID = V.ID)
loop
    if rec.id1 is null
    then
        -- row in U but not in T
    elsif rec.id2 is null
    then
        -- row in T but not in U
    elsif rec.eq is null
        -- row present in both tables
        -- but content mismatch
    end if
end loop

Upvotes: 3

theDbGuy
theDbGuy

Reputation: 931

A 'merge' statement is what u needed.

Here is the syntax:

MERGE INTO TARGET_TABLE
USING SOURCE_TABLE
ON (CONDITION)
WHEN MATCHED THEN
UPDATE SET (DO YOUR UPDATES)
WHEN NOT MATCHED THEN
(INSERT YOUR NEW ROWS)

Google MERGE syntax for more about the statement.

Upvotes: 1

Lalit Kumar B
Lalit Kumar B

Reputation: 49062

Just use MINUS.

query_1
MINUS
query_2

In your case, if you really want to use PL/SQL, then select count into a local variable. Write a logic, if count > 0 then do other stuff.

Upvotes: 0

Dmitriy
Dmitriy

Reputation: 5565

Else the row does not exist
    So add the row to the table compared on

Is this condition means that rows can be missed in both tables? If only in one, then:

insert into t1 (id, text)
select id, text
from t2
minus
select id, text
from t1;

If missed records can be in both tables, you need the same query that inserts into table t2 rows from t1.

If anything is different about them

If you need one action for any amount of different rows, then use something like this:

select count(*)
into a
from t1, t2
where t1.id = t2.id and t1.text <> t2.text;
if a > 0 then
   ...

otherwise:

for i in (
    select *
    from t1, t2
    where t1.id = t2.id and t1.text <> t2.text) loop

  <do something>

end loop;

Upvotes: 1

Related Questions