SQL批量更新表中wRef与tRef字段的优化方法咨询

针对你需要批量更新数千条唯一键（ID）对应字段的场景，完全不需要重复执行单条UPDATE语句，以下是几种更简便高效的实现方式：

方法1：使用UPDATE JOIN VALUES构造临时数据集 #

这是最直接的方式，适合数据量适中（几千条）的场景，一次执行即可完成所有更新：

UPDATE j
SET j.wRef = upd.wRef,
    j.tRef = upd.tRef
FROM [production].[dbo].[jobsTable] j
JOIN (
    VALUES
        ('U19695L', '0004-01', 'T00-07'),
        ('U16129L', '0005-01', 'T10-04'),
        ('U17175L', '0010-01', 'T01-06')
        -- 继续添加所有需要更新的ID及对应wRef、tRef值
) AS upd(ID, wRef, tRef) ON j.ID = upd.ID;

优势： #

• 语法简洁，无需额外创建数据库对象
• 减少多次连接数据库的开销，执行效率远高于单条循环更新

方法2：使用临时表/表变量（适合大量数据） #

如果更新数据超过上万条，使用临时表并建立索引可以进一步提升JOIN效率：

-- 创建带主键的临时表（主键类型需与原表ID匹配）
CREATE TABLE #TempUpdates (
    ID VARCHAR(20) PRIMARY KEY,
    wRef VARCHAR(20),
    tRef VARCHAR(20)
);

-- 批量插入所有更新数据
INSERT INTO #TempUpdates (ID, wRef, tRef)
VALUES
    ('U19695L', '0004-01', 'T00-07'),
    ('U16129L', '0005-01', 'T10-04'),
    ('U17175L', '0010-01', 'T01-06');

-- 执行更新
UPDATE j
SET j.wRef = tu.wRef,
    j.tRef = tu.tRef
FROM [production].[dbo].[jobsTable] j
JOIN #TempUpdates tu ON j.ID = tu.ID;

-- 清理临时表
DROP TABLE #TempUpdates;

方法3：从外部文件批量导入更新（适合超大量数据） #

如果更新数据存储在CSV/Excel等外部文件中，可通过BULK INSERT导入临时表再更新，避免手动编写VALUES语句：

CREATE TABLE #TempUpdates (
    ID VARCHAR(20) PRIMARY KEY,
    wRef VARCHAR(20),
    tRef VARCHAR(20)
);

-- 从CSV文件导入数据，需调整路径和分隔符参数
BULK INSERT #TempUpdates
FROM 'C:\data\updates.csv'
WITH (
    FIELDTERMINATOR = ',',
    ROWTERMINATOR = '\n',
    FIRSTROW = 2 -- 若CSV有表头则跳过第一行
);

-- 执行更新
UPDATE j
SET j.wRef = tu.wRef,
    j.tRef = tu.tRef
FROM [production].[dbo].[jobsTable] j
JOIN #TempUpdates tu ON j.ID = tu.ID;

DROP TABLE #TempUpdates;

关键注意事项： #

• 验证先行：执行更新前，建议先通过SELECT语句确认匹配结果，避免误更新：

  SELECT 
      j.ID, 
      j.wRef AS old_wRef, upd.wRef AS new_wRef,
      j.tRef AS old_tRef, upd.tRef AS new_tRef
  FROM [production].[dbo].[jobsTable] j
  JOIN (VALUES(...)) AS upd(ID, wRef, tRef) ON j.ID = upd.ID;
  

• 事务保护：生产环境操作时，建议开启事务，验证无误后再提交，出错可回滚：

  BEGIN TRANSACTION;
  
  -- 执行更新语句
  UPDATE j SET ...
  
  -- 验证更新结果
  SELECT * FROM [production].[dbo].[jobsTable] WHERE ID IN ('U19695L', 'U16129L');
  
  -- 确认无误后提交，否则回滚
  -- COMMIT TRANSACTION;
  -- ROLLBACK TRANSACTION;
  

• 唯一键约束：确保ID是唯一键，避免单条ID对应多条更新数据，否则会导致更新结果不可预期

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