How can I use SQL to update multiple columns?
source link: https://www.essentialsql.com/how-can-i-use-sql-to-update-multiple-columns/
Go to the source link to view the article. You can view the picture content, updated content and better typesetting reading experience. If the link is broken, please click the button below to view the snapshot at that time.
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.
About the author
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.
Recommend
About Joyk
Aggregate valuable and interesting links.
Joyk means Joy of geeK