Using if/else in insert and update statements

Question

I have a staging table (in SQL SERVER 2008) with nullable fields.
I want to Insert or Update the records from the staging table to main table.
During this I want a comparison to be done

Update main
set main.field1 = (
if(staging.field1 isnull)
    then ---- 
else if(staging.field2 isnull)
    then ---- 
else
    then
)

How can I embed the above condition in my insert and update statements?

Yuck · Accepted Answer · 2012-03-14 16:53:33Z

10

The (sort of) equivalent is to use CASE expressions:

UPDATE main
SET main.field1 =
  CASE
    WHEN staging.field1 IS NULL
      THEN --
    WHEN staging.field2 IS NULL
      THEN --
    ELSE --
  END;

answered Mar 14, 2012 at 16:53

Yuck

51.1k13 gold badges108 silver badges136 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Kamil Over a year ago

sorry i forgot to mention.. the condition must be on priority basis.

Yuck Over a year ago

The priority is in the order you list them. So in this example priority would be given to the field1 expression followed by field2. The first qualifying expression is the one whose value is assigned to main.field1.

onedaywhen · Accepted Answer · 2012-03-16 09:46:33Z

0

MERGE INTO main
   USING staging
      ON main.id = staging.id
WHEN MATCHED THEN
   UPDATE
      SET field1 = COALESCE( staging.field1 , main.field1 ), 
      SET field2 = COALESCE( staging.field2 , main.field2 ), 
      ... ;

edited Mar 16, 2012 at 9:46

answered Mar 16, 2012 at 9:30

onedaywhen

57.4k12 gold badges103 silver badges142 bronze badges

Collectives™ on Stack Overflow

Using if/else in insert and update statements

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related