SSIS,SSRS,SQL Server Information: The Basics of Updating Data in a SQL Server Table

Entry in Greg Larsen's series on Learning T-SQL. The series so far:

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 UPDATE statement is used. This article shows how to use the UPDATE statement to modify data within a SQL Server table.

Syntax of the UPDATE Statement

There are many different options supported by the UPDATE statement. This article will only be showing how to use the basic and most common options of the UPDATE statement. The syntax for that basic UPDATE statement that this article will explore can be found in Figure 1.

UPDATE <object>
    SET <column_name> = {expression}[ ,…n]
    [ <OUTPUT Clause> ]
    [ FROM <table_source> [ ,…n] ]
    [ WHERE  <search_condition> ]

Figure 1: Basic syntax for the UPDATE statement

Where:

<object> – is a table or view name that is being updated.
<column_name> – is a column that will be updated.
{expression} – is a literal string, number, or value that will be used to update the associated <column_name>. This value can be any scalar expression, such as supplied from a variable or even another column in the same or different table.
<table_source> – is a table, view or derived table that will be updated.
<search_condition> – is a constraint that identifies which rows are the target of the UPDATE statement.

To understand the complete syntax of the UPDATE statement refer to the Microsoft Documentation.

Sample Data

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.

The code two tables: dbo.Room and dbo.PriceChanges. 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 database on your instance of SQL Server.

USE tempdb;
GO
CREATE TABLE dbo.Room (
   RoomNum INT,
   Beds INT,
   Sleeps INT,
   StandardRate MONEY,
   PriceChangeDateTime DATETIME2(0),
   RoomDesc VARCHAR(max));
GO
CREATE TABLE dbo.PriceChanges (
   RoomNum VARCHAR(100),
   NewStandardRate MONEY)
GO
-- INSERT Tables with Data
INSERT INTO dbo.Room 
VALUES (1,1,2,125.99,SYSDATETIME(),
        'This room has 1 king size bed, a TV with blue-ray DVD player, and private bathroom with tub'),  
       (2,2,4,175.99, SYSDATETIME(),
        'This room has two double beds, a TV with blue-ray DVD Player, and a private bathroom, with only a shower'),
       (3,2,4,250.99, SYSDATETIME(),
        '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.');
INSERT INTO dbo.PriceChanges    
VALUES (1,150.99),
       (2,198.99),
       (3,299.99);
GO

Listing 1: Sample Tables

Updating a Single Column Value on a Single Row

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 UPDATE statement can be used.

In Listing 2 is an example that will update the value of the StandardRate column to 179.99 for the row where the RoomNum column is equal to 2.

USE tempdb;
GO

UPDATE dbo.Room
   SET StandardRate = 179.99
WHERE RoomNum = 2;
GO

Listing 2: Updating a single column value on a single row

In Listing 2, the UPDATE statement updated the stored value of the StandardRate column in the Room table. The SET clause was used to provide the new rate value of 179.99 for StandardRate column. To identify that only the row with a RoomNum of 2 was to be updated the WHERE clause was used.

Care needs to be taken when issuing an UPDATE statement to ensure you don’t update more rows than intended. For instance, if the WHERE clause was not included in the statement, all the rows in the Room table would have had their StandardRate value set to 179.99. More on this later in the “Concerns with using the UPDATE statement” section below.

Updating Multiple Column Values on a Single Row

The prior example issued an UPDATE statement to modify a single column’s value on a single row. The UPDATE 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 clause, as was done on Listing 3

USE tempdb;
GO
UPDATE dbo.Room
   SET StandardRate = 299.99,
          PriceChangeDateTime = SYSDATETIME()
WHERE RoomNum = 3;
GO

Listing 3: Updating two column values on a single row

This example updates the StandardRate and PriceChangeDateTime columns for only the rows where RoomNum is equal to 3. To provide different values for the two different columns (StandardRate and PriceChangeDateTime) two different values were provided on the SET 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.

Updating Multiple Rows

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 PriceChangeDateTime 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.

USE tempdb;
GO
UPDATE dbo.Room
   SET PriceChangeDateTime = SYSDATETIME()
WHERE RoomNum in (2, 3);
GO

Listing 4: Updating multiple rows with a single UPDATE statement

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 WHERE clause was excluded, like in Listing 5 below, all the PriceChangeDateTime values for all rows in the Room table would have been updated.

USE tempdb;
GO
UPDATE dbo.Room
   SET PriceChangeDateTime = SYSDATETIME();
GO

Listing 5: Updating all rows in Room table

Note: Make sure to review the concerns section below to better understand the pitfalls that can occur when using an UPDATE statement that excludes the WHERE clause.

Update a table with values from another table

In all the examples so far, the SET clause has used a literal value to provide the new value to update the columns in the Room table. Another way to provide the values for an UPDATE statement is to provide them from another table. When the sample data was generated, a table named dbo.PriceChanges was created. This table contains price changes for each of the different rooms. To use the PriceChange table to update rows in the Room table the code in Listing 6 is provided.

USE tempdb;
GO
UPDATE dbo.Room
   SET StandardRate = PriceChanges.NewStandardRate,
       PriceChangeDateTime = SYSDATETIME()
FROM Room JOIN PriceChanges
ON Room.RoomNum = PriceChanges.RoomNum;
GO

Listing 6: Using a table to provide values for UPDATE statement

The FROM clause joins the Room table and the PriceChanges table based on the RoomNum column in both tables. For every row that matches the join criteria SQL Server will take the NewStandardRate column value from the matching PriceChanges table and update the StandardRate column in the Room table. Additionally, the code in Listing 6 also updated the PriceChangeDateTime column with the current date and time, using the SYSDATETIME() function.

In Listing 6 every row in the Room table got a new rate. That is because there was a matching row in the PriceChanges table for every RoomNum. If only a specific RoomNum was required to get a rate update, then a WHERE constraint could be added to the code, as shown in Listing 7.

USE tempdb;
GO
UPDATE dbo.Room
   SET StandardRate = PriceChanges.NewStandardRate,
       PriceChangeDateTime = SYSDATETIME()
FROM Room JOIN PriceChanges
ON Room.RoomNum = PriceChanges.RoomNum
WHERE Room.RoomNum = 2;
GO

Listing 7: Update only RoomNum 2

In Listing 7 the WHERE constraint identified that only RoomNum 2 should be updated.

Partial Updates of Large Data Type Columns

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 WRITE clause to perform a partial or full updates of a large data type columns (varchar(max), nvarchar(max), and varbinary(max); each of which can hold up to 2GB of data in a single value). Here is the syntax for the WRITE clause:

.WRITE(expression, @Offsert, @Length)

The “expression” is the value that will be written to the large data time column, the @Offset identifies the starting position of where the partial update will begin, and the @Length identifies the number of characters that will be replaced.

The code in Listing 8 uses the WRITE clause to change the word “to” in the RoomDesc column to the value “which will” on only the row where the RoomNum is equal to 2.

USE tempdb;
GO
SELECT RoomDesc
FROM   dbo.Room
WHERE  RoomNum = 3;

UPDATE dbo.Room
   SET RoomDesc.WRITE --charindex finds first instance of a value
                  ('which will',charindex('to',RoomDesc)-1,2)
WHERE RoomNum = 3;

SELECT RoomDesc
FROM   dbo.Room
WHERE  RoomNum = 3;
GO

Listing 8: Using the WRITE calls to perform a Partial update

Compare the output and you will see the difference (this is just a portion of the RoomDesc column value before and after the update):

RoomDesc ---------------------------------------------- s out to sleep two. There is also a fireplace

RoomDesk ---------------------------------------------- s out which will sleep two. There is also a f

The WRITE clause cannot be used to update a large data type column to NULL value. This can be demonstrated by running the code in Listing 9.

UPDATE dbo.Room
   SET RoomDesc.WRITE (NULL,0, LEN(RoomDesc))
WHERE RoomNum = 3;
GO
SELECT * FROM dbo.Room WHERE RoomNum = 3;

Listing 9: Trying to set RoomDesc to null

When the code is Listing 9 is executed it runs without error. But by reviewing the output of the SELECT statement, which can be found Report 1, the RoomNum column was set to the empty string, not to a NULL value.

Table Description automatically generated with medium confidence

Report 1: Output of SELECT statement in Listing 9

If a large data type column needs it values set to NULL, then a standard SET clause can be used as in Listing 10.

UPDATE Room
   SET RoomDesc = NULL
WHERE RoomNum = 3;
GO
SELECT * FROM dbo.Room WHERE RoomNum = 3;

Listing 10: Setting a large data type column to null.

The output shown in Report 2 was created when the code in Listing 10 was executed.

Report 2: Output from Listing 10.

In Report 2 the column value for the RoomDesc column is now set to NULL.

Using the TOP Clause

The TOP clause can be used to limit the number of rows being updated. This can be demonstrated by using the code in Listing 11.

USE tempdb;
GO
UPDATE TOP (2) dbo.Room
   SET PriceChangeDateTime = SYSDATETIME();
SELECT * FROM Room;
GO

Listing 11: Using the TOP clause to limit the number of rows being updated

The table Room 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 column values on the three different rows in the output in Report 3.

Report 3: Output when Listing 11 was executed

In Report 3 only the first two rows have updated the PriceChangeDateTime column values. The “TOP (2)” clause in Listing 11 restricted the third row from getting a new datetime stamp value.

Concerns with using the UDPATE statement

Here is a list of a few concerns that might arise when issuing an UPDATE command:

Forgetting to add a WHERE constraint to an UPDATE statement when only a subset of rows needs to be updated. By leaving off the WHERE 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 clause. An all too common mistake.)
Having an incorrect WHERE clause. When a WHERE clause is not correct, either no rows will be updated, or the wrong rows will be updated.

There are a couple of different techniques that can be used to avoid these two issues.

The first one is to take a database/transaction log backup prior to running an untested UPDATE statement. By having a backup, you have a recovery point should a UPDATE 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 statement. It also is not ideal in a very active table where other writes are happening concurrently.

Another method is to first write a SELECT statement using the newly coded WHERE clause. By using a SELECT statement you can verify the correct rows are return. If the wrong rows are displayed, then no problem. Just modify the SELECT statement until the correct WHERE statement is coded, and the correct set of rows are displayed. Once the correct WHERE statement has been generated, change the SELECT statement to an UPDATE statement.

The last method (and one of the best methods when writing scripts to modify a production system) is to wrap the UPDATE statement inside a transaction. First issue a BEGIN TRANACTION, then make your changes. By doing this the UPDATE 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.

Updating data using a view

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 WITH CHECK OPTION, 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.

The important thing to understand for this article is that you can only modify one table object’s data at a time. For example, in listing 12 I will create a view that references dbo.Room and dbo.PriceChanges.

CREATE VIEW dbo.v_room
AS
SELECT Room.RoomNum,
       Room.Beds,
       Room.Sleeps,
       Room.StandardRate,
       Room.PriceChangeDateTime,
       Room.RoomDesc,
       PriceChanges.NewStandardRate
FROM   dbo.Room
                JOIN dbo.PriceChanges
                        ON Room.RoomNum = PriceChanges.RoomNum;

Listing 12: View that references multiple tables

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:

UPDATE dbo.v_room
   SET Beds = 100,
       NewStandardRate = 1000
WHERE  RoomNum = 1;

Listing 13: Update that references multiple tables in the same query

The error output is:

View or function 'dbo.v_room' is not updatable because the modification affects multiple base tables.

To use the view in this particular update scenario, you need to use the code from listing 14:

UPDATE dbo.v_room
  SET Beds = 100
WHERE  RoomNum = 1;

UPDATE dbo.v_room
   SET NewStandardRate = 1000
WHERE  RoomNum = 1;

This works fine.

Note: While it is beyond the scope of this article, you can make any view editable using an instead of trigger object. For more details, go to the documentation for CREATE TRIGGER.

Changing Data with the Basic UPDATE statement

The primary method to maintain existing data in a table as it changes over time is to use the UPDATE statement. An UPDATE statement can update a single column on a single row, or multiple columns on one or more rows. The values used to update a column value can be provided as a scalar expression or can come from column values in another table.

Care needs to be taken when writing UPDATE statements. A badly formed UPDATE statement might update all rows in an entire table, or an incorrect set of rows. Therefore, make sure all UPDATE statements are fully tested prior to running them against your production database. Understanding the basic UPDATE statement is critical in making sure accurate UPDATE statement are written and performed, to maintain database records, as their column values changed over time.

The post The Basics of Updating Data in a SQL Server Table appeared first on Simple Talk.

from Simple Talk https://ift.tt/vSKkfAc
via

SSIS,SSRS,SQL Server Information

Pages

Friday, October 21, 2022

The Basics of Updating Data in a SQL Server Table

Syntax of the UPDATE Statement

Sample Data

Updating a Single Column Value on a Single Row

Updating Multiple Column Values on a Single Row

Updating Multiple Rows

Update a table with values from another table

Partial Updates of Large Data Type Columns

Using the TOP Clause

Concerns with using the UDPATE statement

Updating data using a view

Changing Data with the Basic UPDATE statement

No comments:

Post a Comment

New Jobs Information Posted In Msbi Jobs Tab

ads

site info

Total Pageviews

Copy Right