SQL Server 2019：用同行字段及跨表数据更新现有字段

针对你的需求，核心问题是无主键情况下如何精准匹配行完成更新，以下是分场景的实用方案：

1. 先解决行唯一标识问题 #

因为TABLE1没有主键，且现有字段重复度高，必须给每行生成唯一标识才能确保更新对应行，有两种方式可选：

方式A：新增临时自增ID列（最简单直接） #

如果允许修改表结构，先加一个自增主键列来唯一标记每行：

-- MySQL
ALTER TABLE TABLE1 ADD COLUMN temp_id INT AUTO_INCREMENT PRIMARY KEY;

-- SQL Server
ALTER TABLE TABLE1 ADD temp_id INT IDENTITY(1,1) PRIMARY KEY;

-- PostgreSQL
ALTER TABLE TABLE1 ADD temp_id SERIAL PRIMARY KEY;

方式B：用CTE生成临时行号（无需修改原表） #

如果不能修改原表结构，用CTE结合行号函数生成临时标识，注意排序字段要选能尽量区分行的组合（比如fecha1, hora1, fecha2, hora2, codigo2）：

WITH table1_with_row AS (
    SELECT 
        *,
        ROW_NUMBER() OVER (ORDER BY fecha1, hora1, fecha2, hora2, codigo2) AS row_num
    FROM TABLE1
)

2. 拆分codigo3并关联TABLE2更新 #

结合上面的唯一标识，执行拆分、关联、更新操作：

完整示例（以MySQL为例） #

-- 先新增临时ID（如果用方式A）
ALTER TABLE TABLE1 ADD COLUMN temp_id INT AUTO_INCREMENT PRIMARY KEY;

-- 拆分codigo3、关联TABLE2并更新
WITH split_data AS (
    SELECT
        temp_id,
        SUBSTRING_INDEX(codigo3, '_', 1) AS linea_part, -- 取第一个下划线前的内容
        SUBSTRING_INDEX(SUBSTRING_INDEX(codigo3, '_', 2), '_', -1) AS retiro_part -- 取两个下划线之间的内容
    FROM TABLE1
),
linea_code AS (
    SELECT
        s.temp_id,
        t2.numerical_code, -- 替换为TABLE2中存储数值代码的字段名
        s.retiro_part
    FROM split_data s
    JOIN TABLE2 t2 ON s.linea_part = t2.linea_name -- 替换为TABLE2中与linea_part匹配的字段名
)
UPDATE TABLE1 t1
JOIN linea_code lc ON t1.temp_id = lc.temp_id
SET
    t1.codigo1 = lc.numerical_code,
    t1.codigo4 = lc.retiro_part;

-- 可选：更新完成后删除临时ID列
ALTER TABLE TABLE1 DROP COLUMN temp_id;

不同数据库的拆分函数适配 #

• SQL Server：替换拆分逻辑为：

  LEFT(codigo3, CHARINDEX('_', codigo3) - 1) AS linea_part,
  SUBSTRING(codigo3, CHARINDEX('_', codigo3)+1, CHARINDEX('_', codigo3, CHARINDEX('_', codigo3)+1) - CHARINDEX('_', codigo3) -1) AS retiro_part
  

• PostgreSQL：替换拆分逻辑为：

  (STRING_TO_ARRAY(codigo3, '_'))[1] AS linea_part,
  (STRING_TO_ARRAY(codigo3, '_'))[2] AS retiro_part
  

3. 注意事项 #

• 生成行号时，排序字段的组合必须能唯一区分每行，否则会出现行号重复导致更新错误
• 如果TABLE2中存在linea_part不匹配的情况，需要用LEFT JOIN代替JOIN，并处理numerical_code为NULL的场景（比如保留原codigo1值）

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