CODEWITHSUNDEEP
sql-servert-sqlif-statementconditional-statementscase
apples-oranges
apples-oranges

Reputation: 987

Update fields based on If-Else condition

I have Table A (source) that needs to update Table B (target), depending on a few rules. There is a matching key between both Tables (which is email).

When field check1 equals "Hello" and check2 equals "World" from Table B, then I want to update three fields.

However, if field check1 equals "Hello" and check2 equals "Pineapple" from Table B, then I want to update just one field.

Pseudo code below:

SELECT Name, City, Phone
FROM A

IF B.check1="Hello" AND B.check2="World" THEN
    update B.Name = A.Name, 
    update B.City = A.City,
    update B.Phone = A.Phone

ELSE IF B.check1="Hello" AND B.check2="Pineapple" THEN
    update B.Name = A.Name

ENDIF

INNER JOIN B
ON A.email = B.email

Table examples:

TABLE A

+-----------------------+------+------+-------------+
| Email                 | Name | City | Phone       |
+-----------------------+------+------+-------------+
| [email protected] | John | NYC  | 123-123-123 |
+-----------------------+------+------+-------------+
|                       |      |      |             |
+-----------------------+------+------+-------------+

TABLE B

+-----------------------+--------+--------+------+------+-------------+
| Email                 | check1 | check2 | Name | City | Phone       |
+-----------------------+--------+--------+------+------+-------------+
| [email protected] | Hello  |  World |      |      |             |
+-----------------------+--------+--------+------+------+-------------+
|                       |        |        |      |      |             |
+-----------------------+--------+--------+------+------+-------------+

RESULT (IF first case)

+-----------------------+--------+--------+------+------+-------------+
| Email                 | check1 | check2 | Name | City | Phone       |
+-----------------------+--------+--------+------+------+-------------+
| [email protected] | Hello  |  World | John | NYC  | 123-123-123 |
+-----------------------+--------+--------+------+------+-------------+
|                       |        |        |      |      |             |
+-----------------------+--------+--------+------+------+-------------+

Upvotes: 0

Views: 462

Answers (2)

Dale K
Dale K

Reputation: 27448

You can't conditionally update a column, you either have to update the column or not. But you can conditionally change the value using a case expression. Using your conditions you can either set the specified columns to new values OR keep the old values as follows.

update B set
  [Name] = case when B.check1 = 'Hello' and B.check2 in ('World','Pineapple') then A.[Name] else B.[Name] end
  , City = case when B.check1 = 'Hello' and B.check2 in ('World') then A.City else B.City end
  , Phone = case when B.check1 = 'Hello' and B.check2 in ('World') then A.Phone else B.Phone end
from TableB B
inner join TableA A on A.email = B.email;

Note: You can do the same thing with iif (its just shorthand for case), I prefer case.

Upvotes: 4

Gedza
Gedza

Reputation: 11

You can't this way.

Personally, I would do many updates, but if you insists to do it in one would be :

set
b.Name = a.Name,
b.City = iif(b.check2 = "something", a.City, b.City),
b.Phone = iif(b.check2 = "something", a.Phone, b.Phone)

Upvotes: 1

Related Questions