CODEWITHSUNDEEP
sqlsql-serversql-update
Icaro
Icaro

Reputation: 14845

Update one column only when any other column change

I am trying to create an update statement on SQL Server to set one of the cards as default and all the others as not, however, if I have 3 cards and card 1 set to default and now I want to set card 2 as default I get all 3 cards set as updated, when I should only set cards 1 and 2. I am currently using the following query:

UPDATE Card
  SET                     
     ModifiedDateTimeUtc = @ModifiedDateTimeUtc,
     IsDefault = CASE WHEN Id = @CardId THEN 1 ELSE 0 END
  WHERE CustomerId = @CustomerId;

I need to modify this query so only the cards with values updated get to set a new modified date/time.

PS, I cannot use triggers on this database (unfortunately) so I need to find a solution using a "simple" SQL statement.

Upvotes: 0

Views: 76

Answers (2)

Vali Maties
Vali Maties

Reputation: 399

Maybe it could be write in other ways but you can do like this:

UPDATE Card
SET                     
    ModifiedDateTimeUtc = 
    CASE WHEN Id = @CardId THEN 
        CASE WHEN IsDefault = 1 THEN ModifiedDateTimeUtc ELSE @ModifiedDateTimeUtc END 
    ELSE
        CASE WHEN IsDefault = 0 THEN ModifiedDateTimeUtc ELSE @ModifiedDateTimeUtc END 
    END,
    IsDefault = CASE WHEN Id = @CardId THEN 1 ELSE 0 END
WHERE CustomerId = @CustomerId;

What should do this? If the Id is the same with the parameter and the old value, before update, is 1, datetime will be the same, no update will be made, else (means old value = 0) then it will be updated. And the same for old value= 0...

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269443

Is this what you want?

UPDATE Card
  SET ModifiedDateTimeUtc = @ModifiedDateTimeUtc,
      IsDefault = (CASE WHEN Id = @CardId THEN 1 ELSE 0 END)
  WHERE CustomerId = @CustomerId AND
         IsDefault <> (CASE WHEN Id = @CardId THEN 1 ELSE 0 END);

Usually, I wouldn't repeat a CASE expression in the WHERE. In this case, though, this is the new value for the column, so I think it is a clean expression of the logic.

Upvotes: 0

Related Questions