同表记录更新(用同表的一条记录的值更新本表的其它记录的值)

这个是不同表的的版本

UPDATE Table_1 SET
 price=b.price
FROM Table_1
 INNER JOIN Table_2 b
  ON Table_1.[type] IN (1)
  WHERE b.[type]=1 AND b.price=44

 

同表的是

 DECLARE @CaseID INT
 DECLARE @CaseParentID INT
 SET @CaseID=160
 SET @CaseParentID=122
 
 UPDATE COS_PassengerInfo SET
      [Name]=b.Name
      ,[SexCode]=b.SexCode
      ,[IdentityNo]=b.IdentityNo
      ,[Age]=b.Age
      ,[Room]=b.Room
      ,[Floor]=b.[Floor]
      ,[Block]=b.Block
      ,[Building]=b.Building
      ,[Estate]=b.Estate
      ,[Street]=b.Street
      ,[District]=b.District
      ,[District2Code]=b.District2Code
      ,[Country]=b.Country
      ,[TelNo]=b.TelNo
      ,[EmailAddress]=b.EmailAddress
      ,[ReferredBy]=b.ReferredBy
      ,[LastUpdUser]=b.LastUpdUser
      ,[LastUpdTime]=b.LastUpdTime
 FROM COS_PassengerInfo
  INNER JOIN COS_PassengerInfo b
   --destination
   ON COS_PassengerInfo.CaseID IN
    (
     SELECT CaseID FROM COS_Case
      WHERE CaseParentID=@CaseParentID
       AND CaseID<>@CaseID
       AND OpinionType=2
    )
 ---source
 WHERE b.CaseID=@CaseID