Update a table with result of a select

1
votes

how can I update a column of my table1 with this query?

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

table1 has these columns (column1, column2, column3, column4, column5)

table2 has these columns (column1, column2, column3, column4)

and table2.column4 is foreign key of table1.column1

2
sql-serverdatabase
what you want to update (i.e. in terms of what) ? – Yogesh Sharma
@YogeshSharma I want to update the values of column5 in table 1 (they are integers) – marjan hamidi
Your subquery returns more than one value so the update is not possible – Elham Kohestani
@ElhamKohestani , yes , my select returns a table of integers , how can I insert the integers into table1.column5 – marjan hamidi

2 Answers

2
votes

Use This 

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

Or This

;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
votes

Try this query

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