php - 两个表，更新特定列和/或插入新记录(如果不存在)

Question

我有两个表:NewData 和 RecentData

      NewData

id | Order_Number | Company | SerialNumber | ShipDate
-----------------------------------------------------
 1 | 2323         | Apple   |  SN2324      | 2018-09-12 00:00:00
 2 | 53444        | Telsa   |  SN9098      | 2018-09-12 00:00:00
 3 | 42321        | Stack   |  SN9999      | 2018-09-12 00:00:00



      RecentData

id | Order_Number | Company | SerialNumber | ShipDate
-----------------------------------------
 1 | 2323         | Apple   |              | 2018-05-05 00:00:00
 2 | 53444        | Telsa   | oldserial    | 2018-07-20 00:00:00

当NewData表上的ShipDate发生更改时，我只需要更新ShipDate和<最近数据表中的 em>SerialNumber 列。此外，如果 Order_Number 确实存在于 NewData 表中，但不存在于RecentData 表中，则将整行添加到RecentData 表中。

我尝试了以下方法:

UPDATE RecentData
LEFT JOIN NewData on NewData.Order_Number = RecentData.Order_Number
SET RecentData.Order_Number = NewData.Order_Number;

但这会删除 Order_Number 列中的订单号。

请帮忙!

Answer 1

有多种方法可以解决您的任务。哪种方式更好取决于您的项目具体情况。

方法一:您可以通过“Order_Number”列创建主键并使用“ON DUPLICATE KEY”构造，例如:

INSERT INTO RecentData (Order_Number, Company, SerialNumber, ShipDate)
  (SELECT Order_Number,
          Company,
          SerialNumber,
          ShipDate
   FROM NewData) ON DUPLICATE KEY
UPDATE RecentData.SerialNumber = NewData.SerialNumber,
   RecentData.ShipDate = NewData.ShipDate;

方式 2:您可以使用两个查询:第一个查询更新现有数据，第二个查询插入新数据。例如第一个查询:

UPDATE RecentData
RIGHT JOIN NewData ON NewData.Order_Number = RecentData.Order_Number
SET RecentData.SerialNumber = NewData.SerialNumber,
    RecentData.ShipDate = NewData.ShipDate
WHERE NewData.ShipDate > <last_updating_date>

例如第二个查询:

INSERT INTO RecentData (Order_Number, Company, SerialNumber, ShipDate)
  (SELECT Order_Number,
          Company,
          SerialNumber,
          ShipDate
   FROM NewData
   WHERE ShipDate > <last_updating_date> AND order_number NOT IN
       (SELECT order_number
        FROM RecentData))

方式3:您可以在 UPDATE 事件上创建触发器，例如:

delimiter //
CREATE TRIGGER mytgigger BEFORE UPDATE ON NewData
FOR EACH ROW
BEGIN
    IF (old.ShipDate <> new.ShipDate) THEN
        SET cnt = (SELECT COUNT(*) FROM RecentData WHERE order_number = new.order_number);
        IF cnt > 0 THEN
            UPDATE RecentData
            SET RecentData.SerialNumber = NewData.SerialNumber,
                RecentData.ShipDate = NewData.ShipDate
            WHERE order_number = new.order_number;
        ELSE
            INSERT INTO RecentData (Order_Number, Company, SerialNumber, ShipDate)
            VALUES (new.Order_Number,
                new.Company,
                new.SerialNumber,
                new.ShipDate);
        END IF;
     END IF;
END;//
delimiter ;