如何使用子查询更新多行

Question

当我执行子查询时，一切正常，现在我正在使用相同的查询来基于子查询结果更新列，但它表示子查询返回了超过1行，这使得我应该解决这个问题，这使得sense..How。

begin transaction
update trn_RatingAuto                                                        
set Rate  = 0 
where rate = (
SELECT ar.Rate
FROM trn_account ta
INNER JOIN trn_risk tr  ON ta.AccountId=tr.AccountId
inner join trn_option ot on tr.riskid = ot.riskid
INNER JOIN trn_Rating ra ON ot.RatingId = ra.RatingId
INNER JOIN trn_RatingAuto ar on ra.RatingId = ar.RatingId
where  ar.Rate is null 
)

Answer 1

您需要使用IN而不是=。

update trn_RatingAuto
set Rate  = 0 
where rate IN (
  SELECT ar.Rate
  FROM trn_account ta
  INNER JOIN trn_risk tr  ON ta.AccountId=tr.AccountId
  inner join trn_option ot on tr.riskid = ot.riskid
  INNER JOIN trn_Rating ra ON ot.RatingId = ra.RatingId
  INNER JOIN trn_RatingAuto ar on ra.RatingId = ar.RatingId
  where  ar.Rate is null 
)

编辑：

您也可以通过以下方式进行更新：

update ar
set Rate  = 0 
FROM trn_account ta
INNER JOIN trn_risk tr  ON ta.AccountId=tr.AccountId
inner join trn_option ot on tr.riskid = ot.riskid
INNER JOIN trn_Rating ra ON ot.RatingId = ra.RatingId
INNER JOIN trn_RatingAuto ar on ra.RatingId = ar.RatingId
where  ar.Rate is null

Answer 2

将where从=更改为in

begin transaction
update trn_RatingAuto                                                        
set Rate  = 0 
where rate in (
SELECT ar.Rate
FROM trn_account ta
INNER JOIN trn_risk tr  ON ta.AccountId=tr.AccountId
inner join trn_option ot on tr.riskid = ot.riskid
INNER JOIN trn_Rating ra ON ot.RatingId = ra.RatingId
INNER JOIN trn_RatingAuto ar on ra.RatingId = ar.RatingId
where  ar.Rate is null 
)

Answer 3

如果您想更新多个行，则需要将查询更改为"update from“查询...

sql server 2005 'update from' query

像这样：

 update trn_RatingAuto
   set Rate  = 0  
 from trn_RatingAuto
 join (...