SQL Update with Case, either assign or do nothing

Question

Coming off the back of this question here, is there a way to put a CASE statement into an SQL UPDATE that either update's or does nothing. Currently i have the below:

UPDATE CustomerAddress
SET IsPrimaryAddress = CASE WHEN c2.IsPrimaryAddress=1 THEN 1 ELSE 0 END
FROM CustomerAddress
join #AddressToDeleteMasterOfLesserId p1 on CustomerAddress.Id=p1.[Id that is master]
join CustomerAddress c2 on p1.[Id to delete]=c2.Id 

The problem arises on this line:

SET IsPrimaryAddress = CASE WHEN c2.IsPrimaryAddress=1 THEN 1 ELSE **0** END

When I set it to 0 otherwise. The update should check the first condition, and if so, set to 1, but, if the condition is not met, I don't want to set to 0, I need to retain the original value.

SET IsPrimaryAddress = CASE WHEN c2.IsPrimaryAddress=1 THEN 1 ELSE *CustomerAddress.IsPrimary* END

Doesn't work however, because it gets the value from the FROM statement, rather then the value it has been currently set too. What i want is something like this

SET IsPrimaryAddress = CASE WHEN c2.IsPrimaryAddress=1 THEN 1 ELSE *Do nothing* END

But this is still an assignment, and tries to set to NULL.

Is this above possible?

UPDATE:

Sample data:

pair's of ID's that represent address as so

ID to keep  ID to delete
------------------------
10          21
10          22
11          31
12          41

I'll focus on what happens with 10. So 10 has IsPrimaryAddress=0, 21 has IsPrimaryAddress=1, and 22 has IsPrimaryAddress=0.

What I believe happens is this (in pseudocode):

Set IsPrimaryAddress = if 21.IsPrimaryAddress=1 then set to 21.IsPrimaryAddress else set to 10.IsPrimaryAddress

So we have set 10.IsPrimaryAddress to 1. Now:

Set IsPrimaryAddress = if 22.IsPrimaryAddress=1 then set to 22.IsPrimaryAddress else set to 10.IsPrimaryAddress

Which i would hope 10.IsPrimaryAddress equals 1. But it appears to get the original value which was 0, and now the update has been lost.

Solution 1

Just add a WHERE clause so that you only update the rows that need updating:

UPDATE CustomerAddress c1
  INNER JOIN #AddressToDeleteMasterOfLesserId p1 ON c1.Id = p1.[Id that is master]
  INNER JOIN CustomerAddress c2 ON p1.[Id to delete] = c2.Id
  SET c1.IsPrimaryAddress = 1
  WHERE c2.IsPrimaryAddress = 1

Solution 2

SET CustomerAddress.IsPrimaryAddress = 
      CASE 
         WHEN c2.IsPrimaryAddress=1 
         THEN 1 
         ELSE CustomerAddress.IsPrimaryAddress 
      END

PS: I'm not sure about table name but the idea is to return column value itself to leave it unchanged, this is a common practice.

Solution 3

UPDATE c
SET IsPrimaryAddress = CASE 
  WHEN c2.IsPrimaryAddress = 1 THEN 1 
  ELSE c.IsPrimaryAddress
END
FROM dbo.CustomerAddress AS c
INNER JOIN #AddressToDeleteMasterOfLesserId AS p1 
  ON c.Id = p1.[Id that is master]
INNER JOIN dbo.CustomerAddress AS c2 
  ON p1.[Id to delete] = c2.Id;

Comments

[dann.dev]

Unfortunately this gives an error say that IsPrimaryAddress is ambiguious, this is because the CustomerAddress table is used twice in the join

[sll]

Just prefix by table name, CustomerAddress or which is correct?!

[dann.dev]

Problem with this is it seems to take a select of the data then work from this for the update. You then get it looking at data that may have been updated along the way, so c(1) is originally 0, gets updated to 1, but then has another check where the case isn't true, falls to ELSE c.IsPrimaryAddress which doesn't look at the update data, and set's it back to 0, which is not what I want.

[Adir D]

A single update shouldn't make multiple passes at the same table. Have you proven your theory or is it just a theory? Can you post some sample data in both the base and #temp table that demonstrates this?

[dann.dev]

Then I get the problem with Aaron's answer, where it can set the wrong value because it is not looking at the update values, but rather the From statement

[Adir D]

You're going to need to show an example because I don't think many people are understanding what you mean.

[dann.dev]

I've put some sample data of what I think happens, and I do mean 'think' i am no sql guru, but I have run it several times as above and keep getting different answers that support the theory

[dann.dev]

Haha.... this actually works perfectly... it doesn't quite cover whether I can do it with the case statement above, but if no one comes up with a way to do it I'll accept this answer

[Neil]

@dann.dev This way should also be more efficient, for instance it won't write anything at all if there turn out to be no dependent primary addresses.

[dann.dev]

yes it's definitely a better way, had a feeling I have been trying to be too tricky with my SQL lately, didn't think it would be a CASE statement that got me though!