如何编写正确SQL实现记录日期更新与新记录插入需求？

要同时完成原记录更新和新记录插入，必须用事务保证操作的原子性，避免出现只执行其中一步的情况。以下是针对主流数据库的实现代码：

MySQL 版本 #

START TRANSACTION;

-- 更新目标类型的原记录，将end_date设为当前日期
UPDATE activity_demo
SET end_date = CURDATE()
WHERE activity_type = 'Digital - Content Syndication'
  AND activity_weightage != 2; -- 跳过已设置为2的记录，避免重复操作

-- 仅当更新操作影响了至少一条记录时，插入新记录
INSERT INTO activity_demo (activity_type, activity_weightage, start_date, end_date)
SELECT 'Digital - Content Syndication', 2, CURDATE(), '31/12/2099'
WHERE ROW_COUNT() > 0;

COMMIT;

SQL Server 版本 #

BEGIN TRANSACTION;

UPDATE activity_demo
SET end_date = GETDATE()
WHERE activity_type = 'Digital - Content Syndication'
  AND activity_weightage != 2;

IF @@ROWCOUNT > 0
BEGIN
    INSERT INTO activity_demo (activity_type, activity_weightage, start_date, end_date)
    VALUES ('Digital - Content Syndication', 2, GETDATE(), '31/12/2099');
END

COMMIT TRANSACTION;

Oracle 版本 #

DECLARE
    v_row_count NUMBER;
BEGIN
    UPDATE activity_demo
    SET end_date = SYSDATE
    WHERE activity_type = 'Digital - Content Syndication'
      AND activity_weightage != 2;
    
    v_row_count := SQL%ROWCOUNT;
    
    IF v_row_count > 0 THEN
        INSERT INTO activity_demo (activity_type, activity_weightage, start_date, end_date)
        VALUES ('Digital - Content Syndication', 2, SYSDATE, TO_DATE('31/12/2099', 'DD/MM/YYYY'));
    END IF;
    
    COMMIT;
END;
/

关键说明 #

• 事务包裹：确保更新和插入操作要么全部成功，要么全部回滚，防止数据不一致。
• 过滤条件：添加activity_weightage != 2避免重复处理已经符合要求的记录。
• 条件插入：通过判断更新操作影响的行数，避免在没有符合条件的原记录时插入无效数据。
• 日期函数适配：不同数据库的当前日期函数不同，需根据实际使用的数据库调整（比如Oracle需要用TO_DATE转换目标日期字符串）。

内容的提问来源于stack exchange，提问作者alex