查询占用太多

Question

当我的条件返回结果时，我试图执行更新，问题是当我测试查询时，它永远不会完成。下面是查询；

While(select COUNT(*) from Agreement as agr where agr.Id in (
  select toa.Id from Agreement_TemporaryOnceAgreement as toa where toa.Executed =1)
and agr.EndingDate is null) > 0
begin
DECLARE @AgreementID int;
SET @AgreementID = 
(
select top 1 agr.id from Agreement as agr where agr.Id in (
  select toa.Id from Agreement_TemporaryOnceAgreement as toa where toa.Executed =1)
and agr.EndingDate is null
)
update Agreement SET EndingDate = (
  select tado.Date from TemporaryAgreementsDateOfExecution tado
    where tado.AgreementId = CAST(@AgreementID AS INT))
where Agreement.Id = CAST(@AgreementID AS INT);
end;

Answer 1

你不需要循环。像这样的单个update查询就可以完成工作。

update a
set EndingDate = tado.date
from Agreement a join TemporaryAgreementsDateOfExecution tado
on a.AgreementId = tado.AgreementId

join Agreement_TemporaryOnceAgreement toa
on a.Id = toa.id

where EndingDate is null
and toa.Executed = 1

根据您所使用的RDBMS，可能会有一些细微的变化。