2

How can I use SQL to update multiple columns?

Update Multiple Columns in SQL

You can use the SQL UPDATE statement to update multiple columns. The trick is to specify the columns you wish to update in the SET clause.

All the examples for this lesson are based on Microsoft SQL Server Management Studio and the AdventureWorks database.  You can get started using these free tools using my Guide Getting Started Using SQL Server

Basic Update Command

Here is a simple UPDATE statement to updates a single value:

UPDATE Person.Person
SET FirstName = 'Kenneth'
WHERE BusinessEntityID = 1

Update Multiple Columns

To update multiple columns use the SET clause to specify additional columns. Just like with the single columns you specify a column and its new value, then another set of column and values. In this case each column is separated with a column.

Here we’ll update both the First and Last Names:

UPDATE Person.Person
 Set FirstName = 'Kenneth'
    ,LastName =  'Smith'
 WHERE BusinessEntityID = 1

You can add as many columns as you wish, just be sure to separate them with a comma.

update multiple columns using SQL - command and results

Be careful! You can not easily “undo” your mistakes. One trick I do, to make it easier to test and learn is to “wrap” my update statements in a transaction, that way I can try them out without permanently changing my database.

So here is the same example to update multiple columns, but protected with the transaction…

BEGIN TRANSACTION
 -- Try an update…

 UPDATE Person.Person
 Set FirstName = 'Kenneth'
    ,LastName =  'Smith'
 WHERE BusinessEntityID = 1

 -- Let's look at what we updated
 SELECT * FROM PERSON.PERSON
 WHERE BusinessEntityID = 1

 -- Undo changes…
 ROLLBACK

If you want to learn more about the UPDATE statement I would recommend checking out our article Use SQL To Query and Modify Data.

Kris Wenzel
 

Kris Wenzel has been working with databases over the past 28 years as a developer, analyst, and DBA. He has a BSE in Computer Engineering from the University of Michigan and a MBA from the University of Notre Dame. Kris has written hundreds of blog articles and many online courses. He loves helping others learn SQL.

  • Nick Rentschler says:

    Great article! I learned something new. I was unaware of the ROLLBACK feature of the TRANSACTION. Thanks for sharing!

  • >