CODEWITHSUNDEEP
sqlsql-server-2008
moe
moe

Reputation: 5249

how to update table from select statement in sql server

How can i update a table from select statement results. Here is my select statement:

SELECT count(distinct r.[ID])as Total
  FROM Table1 r left join
  Tabel2 a
  on r.ID = a.ID
  where a.Status is null

and here is like what i want to do and i know it is wrong:

update MyTable
set mycol = total
from
(
SELECT count(distinct r.[ID])as Total
      FROM Table1 r left join
      Tabel2 a
      on r.ID = a.ID
      where a.Status is null)

Upvotes: 0

Views: 5425

Answers (4)

Nico
Nico

Reputation: 1197

you may try something like this:

with "sums"
as
(
  select 
    F."id"
  , "sum" = sum( F."value" ) over ( partition by F."id" )
  from
    "foo" F
)
update 
  B
set
  B."totals" = S."sum"
from
  "bar" B 
  inner join "sums" S
    on S."id" = B."id";

see sql-fiddle here

Upvotes: 1

Rick Petersen
Rick Petersen

Reputation: 744

In the case that, as I assume, you have multiple rows in both tables and you want to update the first table row-by-row with related results from your subquery, you'll want to add a join (assuming that both datasets will have what i am calling 'identifyingfield' below) :

Update MyTable
set mycol = b.total
from
MyTable a
inner join 
(
  SELECT identifyingfield, count(distinct r.[ID])
  FROM Table1 r left join
  Tabel2 a
  on r.ID = a.ID
  where a.Status is null
  group by identifyingfield
) b
ON a.identifyingfield = b.identifyingfield

Upvotes: 1

WhoaItsAFactorial
WhoaItsAFactorial

Reputation: 3558

All you have to do is make a couple tiny changes. Below is the code you will need to use:

update MyTable
set mycol = (SELECT count(distinct r.[ID])as Total
  FROM Table1 r left join
  Tabel2 a
  on r.ID = a.ID
  where a.Status is null)

Upvotes: 1

Guffa
Guffa

Reputation: 700192

Use the subquery in the set:

update MyTable
set mycol = (
  SELECT count(distinct r.[ID])
  FROM Table1 r left join
  Tabel2 a
  on r.ID = a.ID
  where a.Status is null
)

Upvotes: 1

Related Questions