How can I use SQL to update multiple columns?
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.
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.