基于列值更新或插入表

I have two tables BASE and DAILY as shown below:

我有两个表BASE和DAILY,如下所示:

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.

DAILY表每24小时定期更新一次。现在,对于BASE中的每个Cust Id,我必须检查是否在DAILY中修改了IP地址。如果是,则更新BASE中的行。 DAILY中的所有新条目都必须插入BASE。

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

我尝试使用Cursor比较然后更新然后另一个光标插入。

But it is taking lot of time.

但这需要很多时间。

What is the best possible way to do this?

最好的方法是什么?

3 个解决方案

#1

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

您也可以使用MERGE,具体取决于您的数据库系统。 SQL Server语法是

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

Oracle PL / SQL语法似乎大致相同,请看这里

#2

If you just want to update all your BASE table, use an UPDATE to update all the rows in your BASE table.

如果您只想更新所有BASE表,请使用UPDATE更新BASE表中的所有行。

UPDATE `BASE`
SET `IP address` = (SELECT `IP address`
                    FROM DAILY
                    WHERE DAILY.`Cust ID` = `BASE`.`Cust ID`);

Then, use this INSERT INTO query to insert new values that not exists in your table BASE.

然后,使用此INSERT INTO查询插入表BASE中不存在的新值。

INSERT INTO `BASE`
SELECT `Cust ID`, `IP address`
FROM DAILY
WHERE DAILY.`Cust ID` NOT IN (SELECT `Cust ID` FROM BASE);

#3

 SQL>
  declare
  begin
  for i in (select * from daily where ip_add not in (select ip_add from base))
  loop
  update base set ip_add=i.ip_add where custid=i.custid;
  end loop;
  end;

PL/SQL procedure successfully completed.

  SQL> select * from base;

    CUSTID IP_ADD
---------- ----------
         1 10..5.5.5
         2 10..5.5.20   -- updated value from base where ip_add is different 
         3 10..5.5.6

 SQL> select * from base  ;

    CUSTID IP_ADD
---------- ----------
         1 10..5.5.5
         2 10..5.5.20
         4 10..5.5.62

SQL>

#1