更新所有其他记录

问题描述：

我有一个查询下面。我想添加一个条件，如果参数@IsPrestigeFeatured = 0然后更新IsprestigeFeatured列中的所有其他行<>PropertyId为0，并且如果@IsPrestigeFeatured = 1然后IsprestigeFeatured = 1 where propertyId = PropertyId。更新所有其他记录

我查看了case语句/ if语句，但我似乎无法获得正确的语法。欢呼

ALTER PROCEDURE dbo.Update_Property 
@propertyId int, 
@propertyTypeId int, 
@Name ntext, 
@Price int, 
@DescriptionResultsExcerpt text, 
@Description ntext, 
@Characteristics ntext, 
@IsRenovation int, 
@IsCharacter int, 
@IsPrestige int, 
@IsHomepageFeatured int, 
@IsPrestigeFeatured int, 
@CityId int, 
@DepartmentId int, 
@CommuneId int 

As 
UPDATE  Property 
SET  Name = @Name, PropertyTypeID = @propertyTypeId, Price = @Price, 
      DescriptionResultsExcerpt = @DescriptionResultsExcerpt, 
      Description = @Description, Characteristics = @Characteristics, 
      IsRenovation = @IsRenovation, IsCharacter = @IsCharacter, 
      IsPrestige = @IsPrestige, IsHomepageFeatured = @IsHomepageFeatured, 
      IsPrestigeFeatured = @IsPrestigeFeatured, CityId = @CityId, 
      DepartmentId = @DepartmentId, CommuneId = @CommuneId 

FROM Property 
WHERE (PropertyId = @PropertyId)

什么数据库/语言？ MS SQL，mySQL，Oracle？ – Ivo 2010-04-12 08:52:57

我正在使用MSSQL – weaveoftheride 2010-04-12 08:59:24

答

ALTER PROCEDURE dbo.Update_Property  
    @propertyId int, @propertyTypeId int, @Name ntext, @Price int, 
    @DescriptionResultsExcerpt text, @Description ntext, 
    @Characteristics ntext, @IsRenovation int, @IsCharacter int, 
    @IsPrestige int, @IsHomepageFeatured int, 
    @IsPrestigeFeatured int, @CityId int, 
    @DepartmentId int, @CommuneId int  
As 

UPDATE 
    Property 
SET   IsPrestigeFeatured = @IsPrestigeFeatured 
WHERE 
    (@IsPrestigeFeatured = 0 AND PropertyId <> @PropertyId) OR 
    (@IsPrestigeFeatured = 1 AND PropertyId = @PropertyId)

你能给我一个完整的例子基于我上面的SQL查询，因为我只是愚蠢地设法删除我所有的测试数据使用上面的例子 – weaveoftheride 2010-04-12 11:40:05

检查我的更新。备份您的测试数据并运行您的程序。 – Amsakanna 2010-04-12 11:51:33

如果isprestigefeatured = 0，我不想将值更新到所有SET列中。如果isprestigefeatured = 0，我只是想将“0”更新到isprestigefeatured列（如果propertyid propertyid）。如何限制更新到该列。 – weaveoftheride 2010-04-12 11:55:08

答

如果使用TSQL，请您使用http://www.databasejournal.com/features/mssql/article.php/3087431/T-SQL-Programming-Part-1---Defining-Variables-and-IFELSE-logic.htm

基本上只能有一个Prestige-Featured属性，所以如果用户在我的编辑UI中选中了该复选框，那么我想为该行设置列isprestigefeatured = 1，其中propertyid = propertyid和所有其他我想设置的行isprestigefeatured = 0 – weaveoftheride 2010-04-12 09:13:14

更新所有其他记录

相关推荐