几个小嵌套

update TB_AssetsInfo set UseType=0 where
AssetsName='204室'

select * from TB_AssetsInfo where AssetsName='204室'

一条一条更新会死人的！

--更新符合条件的记录

update TB_AssetsInfo set UseType=0 where infoId in(

--找出符合条件记录的ID【zcId就是InfoId】
select zcId from(

--找出符合条件的记录

select * from (

--视图

SELECT     InfoId AS zcId, UseType, AssetsLevel, AssetsName,

--转换一下显示状态

(CASE WHEN UseType = 1 THEN '已租' WHEN UseType = 0 THEN '未租' END) AS UseTypeName,

AssetsNum,
                      CateCode, InfoOrder, ISNULL
                          ((SELECT     dbo.TB_Customer.khName
                              FROM         dbo.TB_Customer INNER JOIN
                                                    dbo.TB_Contract ON dbo.TB_Customer.khId = dbo.TB_Contract.khId
                              WHERE     (dbo.TB_Contract.htId =
                                                        (SELECT     TOP (1) htId
                                                          FROM          dbo.TB_Contract
                                                          WHERE      (htIsEnd = 0) AND (htId IN
                                                                                     (SELECT     htId
                                                                                       FROM          dbo.TB_Contract_Assets
                                                                                       WHERE      (zcId = dbo.TB_AssetsInfo.InfoId)))))), '') AS CustomName, ISNULL
                          ((SELECT     dbo.TB_Contract.htCode
                              FROM         dbo.TB_Customer INNER JOIN
                                                    dbo.TB_Contract ON dbo.TB_Customer.khId = dbo.TB_Contract.khId
                              WHERE     (dbo.TB_Contract.htId =
                                                        (SELECT     TOP (1) htId
                                                          FROM          dbo.TB_Contract
                                                          WHERE      (htIsEnd = 0) AND (htId IN
                                                                                     (SELECT     htId
                                                                                       FROM          dbo.TB_Contract_Assets
                                                                                       WHERE      (zcId = dbo.TB_AssetsInfo.InfoId)))))), '') AS htCode
FROM         dbo.TB_AssetsInfo) as aa
where UseTypeName='已租' and CustomName='') as qq)

转载于:https://www.cnblogs.com/howie/archive/2012/05/07/2487343.html