如何在SQL中基于现有表更新NightTopHit表（无需删表重建）

你的UPDATE写法是错误的，UPDATE语句不能用AS加查询的方式来批量替换整个表的数据。下面给你两种可行的更新方案，根据你的数据库类型和需求选择：

方案一：全量替换（简单通用，适合大多数数据库） #

这种方法先清空现有表的数据，再重新插入最新的聚合结果，操作简单易理解：

步骤1：先创建表时建议指定列名（避免自动生成的列名混乱） #

如果还没创建NightTopHit，或者想修改列名，先执行这个更规范的创建语句：

CREATE TABLE NightTopHit
AS (SELECT 
winNumber AS "Numero Ganador",
COUNT(winNumber) AS "Veces Ganadas"
FROM ganadoresdia
GROUP BY winNumber
HAVING COUNT(winNumber) > 1
ORDER BY COUNT(winNumber) DESC);

步骤2：更新表数据 #

-- 清空表数据（TRUNCATE比DELETE效率更高，适合大数据量）
TRUNCATE TABLE NightTopHit;

-- 插入最新的聚合结果
INSERT INTO NightTopHit ("Numero Ganador", "Veces Ganadas")
SELECT 
winNumber AS "Numero Ganador",
COUNT(winNumber) AS "Veces Ganadas"
FROM ganadoresdia
GROUP BY winNumber
HAVING COUNT(winNumber) > 1
ORDER BY COUNT(winNumber) DESC;

如果你的数据库不支持TRUNCATE，可以用DELETE FROM NightTopHit;替代，但效率会低一些。

方案二：增量更新（适合大数据量，减少数据写入压力） #

如果不想全量清空，只想更新变化的记录（比如已存在的号码更新次数、新增符合条件的号码、删除不再符合条件的号码），可以用对应数据库的增量更新语法：

适用于Oracle、SQL Server、PostgreSQL 15+的MERGE语法 #

-- 匹配现有记录更新次数，无匹配则插入新记录
MERGE INTO NightTopHit t
USING (
    SELECT 
    winNumber AS "Numero Ganador",
    COUNT(winNumber) AS "Veces Ganadas"
    FROM ganadoresdia
    GROUP BY winNumber
    HAVING COUNT(winNumber) > 1
) s
ON (t."Numero Ganador" = s."Numero Ganador")
WHEN MATCHED THEN
    UPDATE SET t."Veces Ganadas" = s."Veces Ganadas"
WHEN NOT MATCHED THEN
    INSERT ("Numero Ganador", "Veces Ganadas")
    VALUES (s."Numero Ganador", s."Veces Ganadas");

-- 删除现在不再符合次数>1条件的记录
DELETE FROM NightTopHit
WHERE "Numero Ganador" NOT IN (
    SELECT winNumber
    FROM ganadoresdia
    GROUP BY winNumber
    HAVING COUNT(winNumber) > 1
);

适用于MySQL的INSERT ON DUPLICATE KEY UPDATE语法 #

首先要给NightTopHit的号码列加唯一约束：

ALTER TABLE NightTopHit ADD PRIMARY KEY ("Numero Ganador");

然后执行增量更新：

-- 更新现有号码的次数，插入新符合条件的号码
INSERT INTO NightTopHit ("Numero Ganador", "Veces Ganadas")
SELECT 
winNumber,
COUNT(winNumber)
FROM ganadoresdia
GROUP BY winNumber
HAVING COUNT(winNumber) > 1
ON DUPLICATE KEY UPDATE "Veces Ganadas" = VALUES("Veces Ganadas");

-- 删除不再符合条件的记录
DELETE FROM NightTopHit
WHERE "Numero Ganador" NOT IN (
    SELECT winNumber
    FROM ganadoresdia
    GROUP BY winNumber
    HAVING COUNT(winNumber) > 1
);

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