首页 文章

使用select的结果更新表

提问于
浏览 次
1

如何使用此查询更新table1的列？

update table1
set table1.column5 = (
select count(*) 
from table2, table1 
where table1.column1 = table2.column4
group by table1.column1)

table1有这些列（column1，column2，column3，column4，column5）

table2有这些列（column1，column2，column3，column4）

和table2.column4是table1.column1的外键

sql-server database

2 回答

  • 2

    用这个

    UPDATE T1
          SET
             column5 = COUNT(1)
       FROM table2 T2
           INNER JOIN table1 T1
             ON T1.column1 = T2.column4
       GROUP BY T2.column4

    或这个

    ;WITH CNT
AS
(
    SELECT
       column4,
       Cnt = COUNT(1)
       FROM table2 T2
       GROUP BY T2.column4
)
update t1
    SET
       column5 = CNT.Cnt
    FROM CNT
       INNER JOIN table1 T1
          ON CNT.column4 = T1.column1
    回复于
  • 1

    试试这个查询

    with cte as (
    select column4, cnt = count(*) 
    from table2
    group by column4
)

update a
    a.column5 = b.cnt
from
    table1 a
    join cte b on a.column1 = b.column4
    回复于

相关问题