update [таблица "партия"] set [кол-во товара в блоке] = (sum([кол-во_товара]) from [таблица "партия-и-упаковка"] where [статус упаковки]="блок")

where [партия.партия]=[партия-и-упаковка.партия] and [партия-и-упаковка.статус]='блок'

create table #t  
(
  pa int, 
  qua int
);

insert into #t (pa,qua) values (1,0), (2,0), (3,0);

with 
pack as (
select * from 
(VALUES(1,27,1),(2,58,0),(2,58,1),(2,65,1),(3,45,0),(3,31,1),(2,76,0),(3,76,1)) AS b(pa,qua,st) 
)

-- обновить таблицу установив значение...
UPDATE #t SET qua = s
-- на основе другой... таблицы
FROM (SELECT p.pa, sum(pack.qua) as s  FROM #t as p
join pack ON p.pa = pack.pa AND st = 1
GROUP BY p.pa) as a
-- при условии 
WHERE #t.pa = a.pa

SELECT * from #t

CREATE TABLE Part ([pa] int,  [qua] int);
insert into Part (pa,qua) values (1,0), (2,0), (3,0);

CREATE TABLE PartU ([pa] int,[qua] int, [st] int); 
insert into PartU (pa,qua,st) values 
(1,27,1),(2,58,0),(2,58,1),(2,65,1),(3,45,0),(3,31,1),(2,76,0),(3,76,1);

SELECT PartU.pa, sum(PartU.qua) as s  
into bufer
FROM PartU 
WHERE PartU.st = 1
GROUP BY PartU.pa;

UPDATE Part set Part.qua = bufer.s 
FROM Part
INNER JOIN bufer
ON (Part.pa = bufer.pa); 

select * from Part