Updating multiple fields based on multiple criteria in a single query

Question

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

For example, if before I did not have a value in field1 for the client, but now I do it, I should be able to update the field "field1". Similarly, I would like to update the columns field2 and field3.

Can I do this in one update.

To update a single column, you can write something like the following:

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 one pass based on different conditions (if there was no data for the fact that this column was previously absent, and now it is available)

Answer 1

 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) 

Answer 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