T-SQL: Using a CASE in an UPDATE statement to update certain columns depending on a condition

sql sql-update case
pqsk picture pqsk · Jan 28, 2011 · Viewed 301.3k times · Source

I am wondering if this is possible at all. I want to update column x if a condition is true, otherwise column y would be updated

UPDATE table SET
     (CASE (CONDITION) WHEN TRUE THEN columnx
                       ELSE columny
      END)
= 25

I have searched all over, tried out some things and am unable to find a solution. I think it's not possible, but I thought I would ask here and see if anyone has done it before. Thanks in advance.

Answer

Adam Robinson picture Adam Robinson · Jan 28, 2011

You can't use a condition to change the structure of your query, just the data involved. You could do this:

update table set
    columnx = (case when condition then 25 else columnx end),
    columny = (case when condition then columny else 25 end)

This is semantically the same, but just bear in mind that both columns will always be updated. This probably won't cause you any problems, but if you have a high transactional volume, then this could cause concurrency issues.

The only way to do specifically what you're asking is to use dynamic SQL. This is, however, something I'd encourage you to stay away from. The solution above will almost certainly be sufficient for what you're after.

Related questions

SQL update from one Table to another based on a ID match

I have a database with account numbers and card numbers. I match these to a file to update any card numbers to the account number, so that I am only working with account numbers. I created a view linking the …

Read More
How can I do an UPDATE statement with JOIN in SQL Server?

I need to update this table in SQL Server with data from its 'parent' table, see below: Table: sale id (int) udid (int) assid (int) Table: ud id (int) assid (int) sale.assid contains the correct value to update ud.…

Read More
Oracle SQL: Update a table with data from another table

Table 1: id name desc ----------------------- 1 a abc 2 b def 3 c adf Table 2: id name desc ----------------------- 1 x 123 2 y 345 In oracle SQL, how do I run an sql update query that can update Table 1 with Table 2's name and desc using …

Read More