如何在SQL中程序化将一行所有列值复制到另一行（SpringBoot环境）

一、核心SQL逻辑 #

直接通过UPDATE JOIN语法实现行数据覆盖，无需临时id，同时跳过主键列：

UPDATE table_1 t1
JOIN table_1 t2 ON t2.id = 1
SET 
  t1.col1 = t2.col1,
  t1.col2 = t2.col2,
  -- 依次列出除id外的所有列
  t1.colX = t2.colX
WHERE t1.id = 2;

二、SpringBoot简洁实现 #

根据你使用的持久化框架，选择对应实现方式：

1. MyBatis 实现

Mapper接口：

@Mapper
public interface Table1Mapper {
    @Update("""
        UPDATE table_1 t1
        JOIN table_1 t2 ON t2.id = #{sourceId}
        SET 
          t1.col1 = t2.col1,
          t1.col2 = t2.col2,
          -- 列出所有非id列
          t1.colX = t2.colX
        WHERE t1.id = #{targetId}
        """)
    int copyRowValues(@Param("sourceId") Integer sourceId, @Param("targetId") Integer targetId);
}

Service调用：

@Service
public class Table1Service {
    private final Table1Mapper table1Mapper;

    public Table1Service(Table1Mapper table1Mapper) {
        this.table1Mapper = table1Mapper;
    }

    public void copyRow(Integer sourceId, Integer targetId) {
        int affectedRows = table1Mapper.copyRowValues(sourceId, targetId);
        if (affectedRows == 0) {
            throw new RuntimeException("目标行不存在");
        }
    }
}

2. JPA 实现

Repository接口：

@Repository
public interface Table1Repository extends JpaRepository<Table1, Integer> {
    @Modifying
    @Transactional
    @Query("""
        UPDATE Table1 t1
        SET 
          t1.col1 = (SELECT t2.col1 FROM Table1 t2 WHERE t2.id = :sourceId),
          t1.col2 = (SELECT t2.col2 FROM Table1 t2 WHERE t2.id = :sourceId),
          -- 列出所有非id列
          t1.colX = (SELECT t2.colX FROM Table1 t2 WHERE t2.id = :sourceId)
        WHERE t1.id = :targetId
        """)
    int copyRowValues(@Param("sourceId") Integer sourceId, @Param("targetId") Integer targetId);
}

Service调用：

@Service
public class Table1Service {
    private final Table1Repository table1Repository;

    public Table1Service(Table1Repository table1Repository) {
        this.table1Repository = table1Repository;
    }

    @Transactional
    public void copyRow(Integer sourceId, Integer targetId) {
        int affectedRows = table1Repository.copyRowValues(sourceId, targetId);
        if (affectedRows == 0) {
            throw new RuntimeException("目标行不存在");
        }
    }
}

3. 动态生成SQL（适配多列场景）

如果表列较多不想手动罗列，可通过JDBC元数据动态生成SQL：

@Service
public class Table1Service {
    private final JdbcTemplate jdbcTemplate;

    public Table1Service(JdbcTemplate jdbcTemplate) {
        this.jdbcTemplate = jdbcTemplate;
    }

    @Transactional
    public void copyRow(Integer sourceId, Integer targetId) {
        // 获取表中所有非id列
        List<String> columns = jdbcTemplate.queryForList(
            "SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'table_1' AND COLUMN_NAME != 'id'",
            String.class
        );

        // 拼接SET子句
        String setClause = columns.stream()
            .map(col -> String.format("t1.%s = t2.%s", col, col))
            .collect(Collectors.joining(", "));

        // 执行更新
        String sql = String.format("""
            UPDATE table_1 t1
            JOIN table_1 t2 ON t2.id = ?
            SET %s
            WHERE t1.id = ?
            """, setClause);

        int affectedRows = jdbcTemplate.update(sql, sourceId, targetId);
        if (affectedRows == 0) {
            throw new RuntimeException("目标行不存在");
        }
    }
}

注意事项 #

• 必须跳过主键id列，避免违反主键约束
• 若涉及关联表约束，需确保更新后数据符合外键规则
• 所有更新操作建议放在事务中执行，保证数据一致性

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