SQL整合数据组

Question

我在一个表中有以下数据

col1      col2              col3
276328  | 999999999999  |   664116
927356  | 999999999999  |   664140
927356  | 999999999999  |   664140
927356  | 999999999999  |   664159
927379  | 999999999999  |   664172

代码：

create table #table (col1 bigint, col2 bigint, col3 bigint)
insert into #table values(276328, 999999999999, 664116)
insert into #table values(927356, 999999999999, 664140)
insert into #table values(927356, 999999999999, 664140)
insert into #table values(927356, 999999999999, 664159)
insert into #table values(927379, 999999999999, 664172)

我需要将Col2更新为Col3中的值，同时对col1进行分组并选择要应用于该子组的最小值(col1)。

276328和927379属于自己的组，但927356是需要分配给col2的最低col3编号的组

因此，更新后的上表应该如下所示：

col1        | col2      | col3
276328      | 664116    | 664116
927356      | 664140    | 664140
927356      | 664140    | 664140
927356      | 664140    | 664159
927379      | 664172    | 664172

我有2m+行要更新，所以它必须是批量更新，而不是循环。

我该如何编写SQL来更新表？

Answer 1

declare @myTable table (col1 bigint, col2 bigint, col3 bigint)
insert @myTable
select 276328  , 999999999999  ,   664116
union select 927356  , 999999999999  ,   664140
union select 927356  , 999999999999  ,   664140
union select 927356  , 999999999999  ,   664159
union select 927379  , 999999999999  ,   664172

update a
set a.col2 = b.col3
from @myTable a
inner join 
(
    select col1, MIN(col3) col3
    from @myTable 
    group by col1
) b
on a.col1 = b.col1


select * from @myTable

Answer 2

update t
set col2 = g.mincol3
from tbl t
join (
        select col1, min(col3) mincol3
        from tbl
        group by col1
    ) g on t.col1 = g.col1

Answer 3

如果您正在使用MySQL

UPDATE table1 a
        INNER JOIN
        (
            SELECT col1, minCol
            FROM
            (
                SELECT col1, MIN(col3) minCol
                FROM tableName
                GROUP BY col1
            ) c
        ) b ON a.col1 = b.col1
SET a.col2 = b.minCol