Is it possible to perform multiple updates with a single UPDATE SQL statement?

by

You can use one statement and a number of case statements

update tbl
  set title = 
    case
      when title in ('a-1', 'a.1') then 'a1'
      when title in ('b-1', 'b.1') then 'b1'
      else title
    end

Of course, this will cause a write on every record, and with indexes, it can be an issue, so you can filter out only the rows you want to change:

update tbl
  set title = 
    case
      when title in ('a-1', 'a.1') then 'a1'
      when title in ('b-1', 'b.1') then 'b1'
      else title
    end
where
  title in ('a.1', 'b.1', 'a-1', 'b-1')

That will cut down the number of writes to the table.

More Related Contents:

  1. Get top 1 row of each group
  2. Insert results of a stored procedure into a temporary table
  3. Referring to a Column Alias in a WHERE Clause
  4. Convert Month Number to Month Name Function in SQL
  5. SET versus SELECT when assigning variables?
  6. How important is the order of columns in indexes?
  7. Generate a resultset of incrementing dates in TSQL
  8. Using the result of an expression (e.g. Function call) in a stored procedure parameter list?
  9. Getting result of dynamic SQL into a variable for sql-server
  10. How to get difference between two rows for a column field?
  11. Better techniques for trimming leading zeros in SQL Server?
  12. How to print VARCHAR(MAX) using Print Statement?
  13. What’s the best way to select the minimum value from several columns?
  14. Concatenate values based on ID
  15. Drop all the tables, stored procedures, triggers, constraints and all the dependencies in one sql statement
  16. For Nvarchar(Max) I am only getting 4000 characters in TSQL?
  17. Splitting delimited values in a SQL column into multiple rows
  18. varbinary to string on SQL Server
  19. How do I group on continuous ranges
  20. How can a LEFT OUTER JOIN return more records than exist in the left table?
  21. Column name or number of supplied values does not match table definition
  22. Incorrect syntax near the keyword ‘with’…previous statement must be terminated with a semicolon
  23. SQL Server 2005 ROW_NUMBER() without ORDER BY
  24. How to find consecutive rows based on the value of a column?
  25. Can an INNER JOIN offer better performance than EXISTS
  26. How to escape square brackets inside square brackets for field name
  27. How do you copy a record in a SQL table but swap out the unique id of the new row?
  28. Cumulative Total in MS Sql server [duplicate]
  29. Memory effective way to read BLOB data in C#/SQL 2005
  30. When to use EXCEPT as opposed to NOT EXISTS in Transact SQL?

Leave a Comment