如何动态遍历多列批量更新Table1中空值为Table2对应值

Got it, let's tackle this problem. You want to automate updating all empty ('') fields in Table1 with matching values from Table2 (where the Part column matches), instead of writing separate UPDATE statements for each field. Here are tailored solutions for common SQL databases:

1. SQL Server #

We’ll use dynamic SQL built from system catalog data to generate the full update statement automatically:

DECLARE @updateSql NVARCHAR(MAX) = N'';

-- Build the SET clause for each updatable column
SELECT @updateSql += N'
  SET t.' + QUOTENAME(c.COLUMN_NAME) + N' = COALESCE(NULLIF(t.' + QUOTENAME(c.COLUMN_NAME) + N', ''''), f.' + QUOTENAME(c.COLUMN_NAME) + N')'
FROM INFORMATION_SCHEMA.COLUMNS c
WHERE c.TABLE_NAME = 'Table1'
  AND c.COLUMN_NAME NOT IN ('Source', 'Part') -- Skip identifier/join columns

-- Assemble the full UPDATE query
SET @updateSql = N'UPDATE t' + @updateSql + N'
FROM Table1 t
JOIN Table2 f ON f.Part = t.Part;';

-- Execute the dynamic SQL
EXEC sp_executesql @updateSql;

Quick Notes: #

• QUOTENAME safely handles column names with special characters (like spaces or hyphens).
• COALESCE(NULLIF(t.Column, ''), f.Column) checks if the Table1 value is empty (converts empty string to NULL) and uses the Table2 value only if needed.
• We exclude Source and Part since those shouldn’t be overwritten (Source identifies the table, Part is our match key).

2. PostgreSQL #

Use PL/pgSQL to loop through columns and build the update statement:

DO $$
DECLARE
  col_name TEXT;
  update_sql TEXT := 'UPDATE table1 t SET ';
BEGIN
  -- Fetch all columns we need to update
  FOR col_name IN
    SELECT column_name
    FROM information_schema.columns
    WHERE table_name = 'table1'
      AND column_name NOT IN ('source', 'part')
  LOOP
    update_sql := update_sql || format('
      %I = COALESCE(NULLIF(t.%I, ''''), f.%I),', col_name, col_name, col_name);
  END LOOP;

  -- Clean up trailing comma and add join logic
  update_sql := RTRIM(update_sql, ',') || '
  FROM table2 f
  WHERE f.part = t.part;';

  -- Run the generated query
  EXECUTE update_sql;
END $$;

3. MySQL #

Create a stored procedure to handle the dynamic update:

DELIMITER //

CREATE PROCEDURE UpdateEmptyFields()
BEGIN
  DECLARE done INT DEFAULT FALSE;
  DECLARE col_name VARCHAR(255);
  DECLARE cur CURSOR FOR
    SELECT column_name
    FROM information_schema.columns
    WHERE table_schema = DATABASE()
      AND table_name = 'Table1'
      AND column_name NOT IN ('Source', 'Part');
  DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;

  SET @update_sql = 'UPDATE Table1 t JOIN Table2 f ON f.Part = t.Part SET ';

  -- Loop through columns to build SET clauses
  OPEN cur;
  read_loop: LOOP
    FETCH cur INTO col_name;
    IF done THEN
      LEAVE read_loop;
    END IF;
    SET @update_sql = CONCAT(@update_sql, 't.', col_name, ' = IF(t.', col_name, ' = '''', f.', col_name, ', t.', col_name, '), ');
  END LOOP;
  CLOSE cur;

  -- Remove trailing comma and execute
  SET @update_sql = LEFT(@update_sql, LENGTH(@update_sql) - 2);
  PREPARE stmt FROM @update_sql;
  EXECUTE stmt;
  DEALLOCATE PREPARE stmt;
END //

DELIMITER ;

-- Run the procedure
CALL UpdateEmptyFields();

Core Takeaways for All Solutions: #

• We leverage INFORMATION_SCHEMA.COLUMNS to dynamically get column lists, so this will adapt if you add more InfoX fields later.
• The logic preserves existing non-empty values in Table1—only empty fields get replaced.

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