Update multiple fields based on multiple Criteria in one Query

Question

I am trying to update 3 different columns in a table based on 3 different conditions in the where clause. (I have the updated data in a different table, so I am joining them on the primary keys)

For example, if I did not have a value previously in field1 for a customer but now I do, I should be able to update the column 'field1'. Similarly, I would like to update columns field2 and field3.

Can I accomplish this in a single Update statement.

To Update one column you can write something like this:

Update tblCustomer 
SET tblCustomer.Order_Date = tblCustomerInfo.Order_Date
FROM tblCustomer 
LEFT JOIN tblCustomerInfo ON (tblCustomer.CustomerID = tblCustomerInfo.CustomerID)
WHERE tblCustomer.Order_Date <> tblCustomerInfo.Order_Date 
  AND tblCustomer.Order_Date is NULL;

How about updating 3 different Columns in single go based on different where conditions (if the data was missing for that column was missing previously, and is now available)

Macmade · Accepted Answer · 2012-10-03 17:46:24Z

25

UPDATE categories
    SET display_order = CASE id
        WHEN 1 THEN 3
        WHEN 2 THEN 4
        WHEN 3 THEN 5
    END,
    title = CASE id
        WHEN 1 THEN 'New Title 1'
        WHEN 2 THEN 'New Title 2'
        WHEN 3 THEN 'New Title 3'
    END
WHERE id IN (1,2,3)

edited Oct 3, 2012 at 17:46

Macmade

54.3k14 gold badges114 silver badges127 bronze badges

answered Oct 3, 2012 at 5:17

Thushar

2662 silver badges2 bronze badges

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

1 Comment

nam Aug 4 at 0:44

I think you need to include the ELSE display_order with the first CASE block, and ELSE title in the second CASE block. Otherwise, title will be NULL in first case, and display_order will be NULL in the second case.

Deval Ringwala · Accepted Answer · 2012-09-10 21:23:31Z

2

You can update multiple columns

 UPDATE [t1]
  SET field1 = t2.field1,
      field2 = CASE WHEN <field 2 changed> THEN t2.field2 ELSE t1.field2 END,
      field3 = CASE WHEN t1.field3 <> t2.field3 THEN t2.field3 else t1.field3 END
  FROM <table1> as t1
  LEFT JOIN <table2> as t2 on t1.key1 = t2.key1

answered Sep 10, 2012 at 21:23

Deval Ringwala

3581 silver badge5 bronze badges

Collectives™ on Stack Overflow

Update multiple fields based on multiple Criteria in one Query

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related