Search code examples
insertplsqlcursors

Update or Insert on a Table based on a column value

I have two tables BASE and DAILY as shown below:

BASE    
Cust ID  IP address
1        10.5.5.5
2        10.5.5.50
3        10.5.5.6

DAILY   
Cust ID  IP address
1        10.5.5.5
2        10.5.5.70
4        10.5.5.67

The table DAILY is periodically refreshed every 24 hours. Now for every Cust Id in BASE I have to check if the IP address is modified in DAILY. If yes then update the row in BASE. All the new entries in DAILY have to be inserted into BASE.

I have tried this using a Cursor comparing and then updating and then another cursor for insertion.

But it is taking lot of time.

What is the best possible way to do this?

Solution

  • You could also use MERGE depending on your database system. SQL Server syntax would be

    MERGE INTO BASE B
USING DAILY D
ON D.CustId = B.CustId
WHEN NOT MATCHED THEN
INSERT (CustId, Ip) VALUES (D.CustId, D.Ip)
WHEN MATCHED AND D.Ip <> B.Ip THEN
UPDATE SET B.Ip = D.Ip;

    Oracle PL/SQL syntax seems to be much the same, take a look here