CODEWITHSUNDEEP
sql-serverdatabase
marjan hamidi
marjan hamidi

Reputation: 13

Update a table with result of a select

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

Upvotes: 1

Views: 132

Answers (2)

Jayasurya Satheesh
Jayasurya Satheesh

Reputation: 8033

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

Upvotes: 2

uzi
uzi

Reputation: 4146

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

Upvotes: 1

Related Questions