T-SQL 進階技巧：UPDATE/DELETE 搭配 INNER JOIN 與刪除重複資料實戰

UPDATE 搭配 INNER JOIN

UPDATE a
SET a.EmployeeStrId = b.PressWorkId
FROM CEmployee a
INNER JOIN CCard b ON a.CardId = b.CardId;

DELETE 搭配 INNER JOIN

DELETE a
FROM EConsumeData a
INNER JOIN CCard b ON a.CardId = b.CardId;

語法關鍵：DELETE 別名 FROM 表別名 JOIN ...，而非 DELETE FROM 表 JOIN ...。

刪除重複資料兩大經典模式

模式一：暫存表法（適用可停機維護）

1. 查詢不重複資料存入暫存表
2. 清空正式表
3. INSERT INTO 正式表 SELECT * FROM 暫存表

模式二：直接刪除法（線上業務不可停機）

找出重複群組的「標識欄位」（如時間戳），保留極值行，刪除其餘。

實戰範例：保留最大消費日期的記錄

-- 1. 找出重複群組及保留行 (最大消費日期)
SELECT DeviceId, RecordId, CardId, CardValue, UseCount, ConsumeMoney,
       COUNT(CardId) AS Cnt, MAX(ConsumeDate) AS KeepDate
INTO #TmpKeep
FROM EConsumeData
WHERE CONVERT(char(10), ConsumeDate, 21) BETWEEN '2006-01-01' AND '2007-12-31'
GROUP BY DeviceId, RecordId, CardId, CardValue, UseCount, ConsumeMoney
HAVING COUNT(CardId) > 1
ORDER BY CardId, UseCount;
GO

-- 2. 刪除重複行 (保留 KeepDate 那一筆)
DELETE A
FROM EConsumeData A
INNER JOIN #TmpKeep B
  ON A.DeviceId = B.DeviceId
  AND A.RecordId = B.RecordId
  AND A.CardId = B.CardId
  AND A.CardValue = B.CardValue
  AND A.UseCount = B.UseCount
  AND A.ConsumeMoney = B.ConsumeMoney
  AND A.ConsumeDate <> B.KeepDate;  -- 若改 = 則保留暫存表那筆
GO

DROP TABLE #TmpKeep;

關鍵技巧總結

• DELETE 別名 FROM ... JOIN ... 可精準刪除主表資料
• 暫存表保留「基準行」，再用 JOIN 刪除其餘重複行
• 條件欄位需覆蓋所有定義重複的欄位，確保精準匹配
• HAVING COUNT(*) > 1 找出重複群組