Once data is inserted into a table, data typically needs to be maintained as time goes on. To make changes to an existing row or a number of rows, in a table, the There are many different options supported by the Figure 1: Basic syntax for the <\/strong> Where:<\/p>\n To understand the complete syntax of the To demonstrate how to update the values in a table, sample data will be needed. The code in Listing 1 will create and populate two different sample tables that will be used in this article.<\/p>\n The code two tables: Listing 1: Sample Tables<\/strong><\/strong><\/p>\n There are times when data in a single row of a table needs to be changed. It might need to be changed because it was entered incorrectly, or as time goes on the data needs to change to reflect the current situation. When a single column value, in a single row, needs to be changed the In Listing 2 is an example that will update the value of the Listing 2: Updating a single column value on a single row<\/p>\n In Listing 2, the Care needs to be taken when issuing an The prior example issued an Listing 3: Updating two column values on a single row<\/strong><\/strong><\/p>\n This example updates the Each example so far has only updated a single row. There are times when an applications might need to make the same column value changes to more than a single row. For instance, suppose the sample table needed to have the Listing 4: Updating multiple rows with a single When the code in Listing 4 is executed the current date and time value will be set for room numbers 2 and 3. If the Listing 5: Updating all rows in Note<\/strong>: Make sure to review the concerns section below to better understand the pitfalls that can occur when using an In all the examples so far, the Listing 6: Using a table to provide values for The In Listing 6 every row in the Listing 7: Update only In Listing 7 the Certain datatypes can hold a large amount of data and sometimes it is better to not try to download and update the entire value. With the introduction of SQL Server 2005, Microsoft introduced the The \u201cexpression\u201d is the value that will be written to the large data time column, the The code in Listing 8 uses the Listing 8: Using the Compare the output and you will see the difference (this is just a portion of the The Listing 9: Trying to set When the code is Listing 9 is executed it runs without error. But by reviewing the output of the Report 1: Output of <\/strong> If a large data type column needs it values set to Listing 10: Setting a large data type column to null.<\/p>\n The output shown in Report 2 was created when the code in Listing 10 was executed.<\/p>\n Report 2: Output from Listing 10.<\/strong><\/p>\n In Report 2 the column value for the The Listing 11: Using the TOP clause to limit the number of rows being updated<\/p>\n The table Report 3: Output when Listing 11 was executed<\/strong><\/p>\n In Report 3 only the first two rows have updated the Here is a list of a few concerns that might arise when issuing an There are a couple of different techniques that can be used to avoid these two issues.<\/p>\n The first one is to take a database\/transaction log backup prior to running an untested Another method is to first write a The last method (and one of the best methods when writing scripts to modify a production system) is to wrap the Updating data accessing a view can be a very useful tool. Using a view, you are able to enforce criteria on the user by embedding it in the view. Using the The important thing to understand for this article is that you can only modify one table object\u2019s data at a time. For example, in listing 12 I will create a view that references Listing 12: View that references multiple tables<\/p>\n One of the nice things about view objects is that they hide the implementation details from the user. However, if a user wrote the query as shown in Listing 13, they will get a confusing error message:<\/p>\n Listing 13: Update that references multiple tables in the same query<\/p>\n The error output is:<\/p>\n To use the view in this particular update scenario, you need to use the code from listing 14:<\/p>\n This works fine.<\/p>\nUPDATE<\/code> statement is used. This article shows how to use the UPDATE<\/code> statement to modify data within a SQL Server table.<\/p>\nSyntax of the UPDATE Statement<\/h2>\n
UPDATE<\/code> statement. This article will only be showing how to use the basic and most common options of the UPDATE<\/code> statement. The syntax for that basic UPDATE statement that this article will explore can be found in Figure 1.<\/p>\nUPDATE <object>\r\n SET <column_name> = {expression}[ ,\u2026n]\r\n [ <OUTPUT Clause> ]\r\n [ FROM <table_source> [ ,\u2026n] ]\r\n [ WHERE <search_condition> ] <\/pre>\nUPDATE<\/code> statement<\/strong><\/p>\n\n
<object><\/code> – is a table or view name that is being updated.<\/li>\n<column_name><\/code> – is a column that will be updated.<\/li>\n{expression}<\/code> – is a literal string, number, or value that will be used to update the associated <column_name>. <\/code>This value can be any scalar expression, such as supplied from a variable or even another column in the same or different table.<\/li>\n<table_source><\/code> – is a table, view or derived table that will be updated.<\/li>\n<search_condition><\/code> – is a constraint that identifies which rows are the target of the UPDATE<\/code> statement.<\/li>\n<\/ul>\nUPDATE<\/code> statement refer to the Microsoft Documentation<\/a>.<\/p>\nSample Data<\/h2>\n
dbo.Room<\/code> and dbo.PriceChanges<\/code>. These tables will be used in the examples found in this article. If you want to follow along and run the examples contained in this article, use this code in Listing 1, to create these sample tables in the tempdb<\/code> database on your instance of SQL Server.<\/p>\nUSE tempdb;\r\nGO\r\nCREATE TABLE dbo.Room (\r\n RoomNum INT,\r\n Beds INT,\r\n Sleeps INT,\r\n StandardRate MONEY,\r\n PriceChangeDateTime DATETIME2(0),\r\n RoomDesc VARCHAR(max));\r\nGO\r\nCREATE TABLE dbo.PriceChanges (\r\n RoomNum VARCHAR(100),\r\n NewStandardRate MONEY)\r\nGO\r\n-- INSERT Tables with Data\r\nINSERT INTO dbo.Room \r\nVALUES (1,1,2,125.99,SYSDATETIME(),\r\n 'This room has 1 king size bed, a TV with blue-ray DVD player, and private bathroom with tub'), \r\n (2,2,4,175.99, SYSDATETIME(),\r\n 'This room has two double beds, a TV with blue-ray DVD Player, and a private bathroom, with only a shower'),\r\n (3,2,4,250.99, SYSDATETIME(),\r\n 'This room is a suite. It has a private bedroom with king size bed, and private bathroom. In the living space there is a sofa that pulls out to sleep two. There is also a fireplace and a couple of easy chairs. There are two TVs with blue-ray players. Has a kitchen with dishes.');\r\nINSERT INTO dbo.PriceChanges\t\r\nVALUES (1,150.99),\r\n (2,198.99),\r\n (3,299.99);\r\nGO<\/pre>\n
Updating a Single Column Value on a Single Row<\/h2>\n
UPDATE<\/code> statement can be used.<\/p>\nStandardRate<\/code> column to 179.99<\/code> for the row where the RoomNum<\/code> column is equal to 2.<\/p>\nUSE tempdb;\r\nGO\r\n\r\nUPDATE dbo.Room\r\n SET StandardRate = 179.99\r\nWHERE RoomNum = 2;\r\nGO<\/pre>\n
UPDATE<\/code> statement updated the stored value of the StandardRate<\/code> column in the Room<\/code> table. The SET<\/code> clause was used to provide the new rate value of 179.99<\/code> for StandardRate<\/code> column. To identify that only the row with a RoomNum<\/code> of 2<\/code> was to be updated the WHERE<\/code> clause was used.<\/p>\nUPDATE<\/code> statement to ensure you don\u2019t update more rows than intended. For instance, if the WHERE<\/code> clause was not included in the statement, all the rows in the Room<\/code> table would have had their StandardRate<\/code> value set to 179.99<\/code>. More on this later in the \u201cConcerns with using the UPDATE<\/code> statement\u201d section below.<\/p>\nUpdating Multiple Column Values on a Single Row<\/h2>\n
UPDATE<\/code> statement to modify a single column\u2019s value on a single row. The UPDATE<\/code> statement allows multiple column values on a single row to be updated at the same time. When multiple columns need to be updated, the additional columns and values can be added to the SET<\/code> clause, as was done on Listing 3<\/p>\nUSE tempdb;\r\nGO\r\nUPDATE dbo.Room\r\n SET StandardRate = 299.99,\r\n \t PriceChangeDateTime = SYSDATETIME()\r\nWHERE RoomNum = 3;\r\nGO<\/pre>\n
StandardRate<\/code> and PriceChangeDateTime<\/code> columns for only the rows where RoomNum<\/code> is equal to 3<\/code>. To provide different values for the two different columns (StandardRate<\/code> and PriceChangeDateTime<\/code>) two different values were provided on the SET<\/code> clause. Each column name\/value pair is separated by a comma. This example only updated two different columns. If more than 2 columns needed to have their values updated then more name\/values pairs could be added, where each pair is separated by a comma.<\/p>\nUpdating Multiple Rows<\/h2>\n
PriceChangeDateTime<\/code> column value changed to the current date and time, on the two different rows that were updated in the last two examples. This could be accomplished by running the code in Listing 4.<\/p>\nUSE tempdb;\r\nGO\r\nUPDATE dbo.Room\r\n SET PriceChangeDateTime = SYSDATETIME()\r\nWHERE RoomNum in (2, 3);\r\nGO<\/pre>\n
UPDATE<\/code> statement<\/p>\nWHERE<\/code> clause was excluded, like in Listing 5 below, all the PriceChangeDateTime<\/code> values for all rows in the Room<\/code> table would have been updated.<\/p>\nUSE tempdb;\r\nGO\r\nUPDATE dbo.Room\r\n SET PriceChangeDateTime = SYSDATETIME();\r\nGO<\/pre>\n
Room<\/code> table<\/p>\nUPDATE<\/code> statement that excludes the WHERE<\/code> clause.<\/p>\nUpdate a table with values from another table<\/h2>\n
SET<\/code> clause has used a literal value to provide the new value to update the columns in the Room<\/code> table. Another way to provide the values for an UPDATE<\/code> statement is to provide them from another table. When the sample data was generated, a table named dbo.PriceChanges<\/code> was created. This table contains price changes for each of the different rooms. To use the PriceChange<\/code> table to update rows in the Room<\/code> table the code in Listing 6 is provided.<\/p>\nUSE tempdb;\r\nGO\r\nUPDATE dbo.Room\r\n SET StandardRate = PriceChanges.NewStandardRate,\r\n PriceChangeDateTime = SYSDATETIME()\r\nFROM Room JOIN PriceChanges\r\nON Room.RoomNum = PriceChanges.RoomNum;\r\nGO<\/pre>\n
UPDATE<\/code> statement<\/p>\nFROM<\/code> clause joins the Room<\/code> table and the PriceChanges<\/code> table based on the RoomNum<\/code> column in both tables. For every row that matches the join criteria SQL Server will take the NewStandardRate<\/code> column value from the matching PriceChanges<\/code> table and update the StandardRate<\/code> column in the Room <\/em>table. Additionally, the code in Listing 6 also updated the PriceChangeDateTime<\/code> column with the current date and time, using the SYSDATETIME<\/code>() function.<\/p>\nRoom<\/code> table got a new rate. That is because there was a matching row in the PriceChanges<\/code> table for every RoomNum<\/code>. If only a specific RoomNum<\/code> was required to get a rate update, then a WHERE<\/code> constraint could be added to the code, as shown in Listing 7.<\/p>\nUSE tempdb;\r\nGO\r\nUPDATE dbo.Room\r\n SET StandardRate = PriceChanges.NewStandardRate,\r\n PriceChangeDateTime = SYSDATETIME()\r\nFROM Room JOIN PriceChanges\r\nON Room.RoomNum = PriceChanges.RoomNum\r\nWHERE Room.RoomNum = 2;\r\nGO<\/pre>\n
RoomNum<\/code> 2<\/p>\nWHERE<\/code> constraint identified that only RoomNum<\/code> 2<\/code> should be updated.<\/p>\nPartial Updates of Large Data Type Columns<\/h2>\n
WRITE<\/code> clause to perform a partial or full updates of a large data type columns (varchar(max)<\/code>, nvarchar(max)<\/code>, and varbinary(max)<\/code>; each of which can hold up to 2GB of data in a single value). Here is the syntax for the WRITE<\/code> clause:<\/p>\n.WRITE(expression, @Offsert, @Length)<\/code><\/p>\n@Offset<\/code> identifies the starting position of where the partial update will begin, and the @Length<\/code> identifies the number of characters that will be replaced.<\/p>\nWRITE<\/code> clause to change the word \u201cto\u201d in the RoomDesc<\/code> column to the value \u201cwhich will\u201d on only the row where the RoomNum<\/code> is equal to 2<\/code>.<\/p>\nUSE tempdb;\r\nGO\r\nSELECT RoomDesc\r\nFROM dbo.Room\r\nWHERE RoomNum = 3;\r\n\r\nUPDATE dbo.Room\r\n SET RoomDesc.WRITE --charindex finds first instance of a value\r\n ('which will',charindex('to',RoomDesc)-1,2)\r\nWHERE RoomNum = 3;\r\n\r\nSELECT RoomDesc\r\nFROM dbo.Room\r\nWHERE RoomNum = 3;\r\nGO<\/pre>\nWRITE<\/code> calls to perform a Partial update<\/p>\nRoomDesc<\/code> column value before and after the update):<\/p>\nRoomDesc
\n----------------------------------------------
\ns out to sleep two. There is also a fireplace
\n<\/code><\/p>\nRoomDesk
\n----------------------------------------------
\ns out which will sleep two. There is also a f<\/code><\/p>\nWRITE<\/code> clause cannot be used to update a large data type column to NULL<\/code> value. This can be demonstrated by running the code in Listing 9.<\/p>\nUPDATE dbo.Room\r\n SET RoomDesc.WRITE (NULL,0, LEN(RoomDesc))\r\nWHERE RoomNum = 3;\r\nGO\r\nSELECT * FROM dbo.Room WHERE RoomNum = 3;<\/pre>\n
RoomDesc<\/code> to null<\/p>\nSELECT<\/code> statement, which can be found Report 1, the RoomNum<\/code> column was set to the empty string, not to a NULL<\/code> value.<\/p>\n![\"Table\n\nDescription](\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/2022\/10\/table-description-automatically-generated-with-me.png\")
<\/p>\nSELECT<\/code> statement in Listing 9<\/strong><\/p>\nNULL,<\/code> then a standard SET<\/code> clause can be used as in Listing 10.<\/p>\nUPDATE Room\r\n SET RoomDesc = NULL\r\nWHERE RoomNum = 3;\r\nGO\r\nSELECT * FROM dbo.Room WHERE RoomNum = 3;<\/pre>\n
![](\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/2022\/10\/word-image-29.png\")
<\/p>\nRoomDesc<\/code> column is now set to NULL.<\/p>\nUsing the TOP Clause<\/h2>\n
TOP<\/code> clause can be used to limit the number of rows being updated. This can be demonstrated by using the code in Listing 11.<\/p>\nUSE tempdb;\r\nGO\r\nUPDATE TOP (2) dbo.Room\r\n SET PriceChangeDateTime = SYSDATETIME();\r\nSELECT * FROM Room;\r\nGO<\/pre>\n
Room<\/code> only has 3 rows of data. When the code is listing 9 is run only the first two rows will be update. This can be seen by revieing the PriceChangeDateTime<\/code> column values on the three different rows in the output in Report 3.<\/p>\n![](\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/2022\/10\/word-image-30.png\")
<\/p>\nPriceChangeDateTime<\/code> column values. The \u201cTOP (2)<\/code>\u201d clause in Listing 11 restricted the third row from getting a new datetime stamp value.<\/p>\nConcerns with using the UDPATE statement<\/h2>\n
UPDATE<\/code> command:<\/p>\n\n
WHERE<\/code> constraint to an UPDATE<\/code> statement when only a subset of rows needs to be updated. By leaving off the WHERE<\/code> clause causes all rows in the target table will be updated. (This is a common database programmer error to think an entire statement is highlighted, but miss the WHERE<\/code> clause. An all too common mistake.)<\/li>\nWHERE<\/code> clause. When a WHERE<\/code> clause is not correct, either no rows will be updated, or the wrong rows will be updated.<\/li>\n<\/ul>\nUPDATE<\/code> statement. By having a backup, you have a recovery point should a UPDATE<\/code> statement update rows that should have been updated. Even though this method technically works, it might not be the most elegant solution to resolving a poorly coded UPDATE<\/code> statement. It also is not ideal in a very active table where other writes are happening concurrently.<\/p>\nSELECT<\/code> statement using the newly coded WHERE<\/code> clause. By using a SELECT<\/code> statement you can verify the correct rows are return. If the wrong rows are displayed, then no problem. Just modify the SELECT<\/code> statement until the correct WHERE<\/code> statement is coded, and the correct set of rows are displayed. Once the correct WHERE<\/code> statement has been generated, change the SELECT<\/code> statement to an UPDATE<\/code> statement.<\/p>\nUPDATE<\/code> statement inside a transaction. First issue a BEGIN TRANACTION<\/code>, then make your changes. By doing this the UPDATE<\/code> statement can be rolled back if it updated the rows or values incorrectly. Just be aware that uncommitted changes can block other users depending on how your server and databases are configured.<\/p>\nUpdating data using a view<\/h2>\n
WITH CHECK OPTION<\/code>, you can ensure that users can only modify (or insert) data that they can see based on any filtering of rows in the view. You can read more about view objects in the Microsoft documentation<\/a>.<\/p>\ndbo.Room<\/code> and dbo.PriceChanges<\/code>.<\/p>\nCREATE VIEW dbo.v_room\r\nAS\r\nSELECT Room.RoomNum,\r\n Room.Beds,\r\n Room.Sleeps,\r\n Room.StandardRate,\r\n Room.PriceChangeDateTime,\r\n Room.RoomDesc,\r\n PriceChanges.NewStandardRate\r\nFROM dbo.Room\r\n\t\tJOIN dbo.PriceChanges\r\n\t\t\tON Room.RoomNum = PriceChanges.RoomNum;<\/pre>\n
UPDATE dbo.v_room\r\n SET Beds = 100,\r\n NewStandardRate = 1000\r\nWHERE RoomNum = 1;<\/pre>\n
View or function 'dbo.v_room' is not updatable because the modification affects multiple base tables.<\/code><\/p>\nUPDATE dbo.v_room\r\n SET Beds = 100\r\nWHERE RoomNum = 1;\r\n\r\nUPDATE dbo.v_room\r\n SET NewStandardRate = 1000\r\nWHERE RoomNum = 1;<\/pre>\n