Updating data in a SQL Server table is a common task in database management. Whether you need to change the values of a single column for a single record or update multiple columns for all rows, SQL provides various ways to accomplish this. In this tutorial, we will explore different techniques for updating data in SQL Server tables.

Basic SQL UPDATE Syntax

The basic syntax for updating data in a SQL Server table is as follows:

UPDATE schema.TableName
SET Column2 = 'NewValue'
WHERE Column1 = 'Condition';

In this syntax:

• schema.TableName refers to the target table where the data will be updated.
• Column2 is the column that will be updated with the new value.
• 'NewValue' is the new value that will replace the existing value in Column2.
• Column1 is an optional condition that limits the scope of the records to be updated. Only the records that meet the specified condition will be affected.

Example: Updating a Single Column for a Single Row

Let’s start with a simple example of updating a single column for a single row in a table. Suppose we have a table called MySalesPerson with columns BusinessEntityID and TerritoryID. We want to update the TerritoryID to 1 for the record where BusinessEntityID is 285.

UPDATE MySalesPerson
SET TerritoryID = 1
WHERE BusinessEntityID = 285;

This query will update the TerritoryID to 1 for the specified record.

Example: Updating a Column for All Rows

Sometimes, you may need to update a column for all rows in a table. In such cases, you can omit the WHERE clause to update all records. However, be cautious when using this approach to avoid unintentionally modifying all data in the column.

UPDATE MySalesPerson
SET SalesQuota = 30000.00;

This query will update the SalesQuota column for all rows in the MySalesPerson table to $30,000.

Example: Updating Data Based on a Join

In some scenarios, you may need to update data in a table based on a join with another table. This can be achieved by using a join in the UPDATE statement.

UPDATE MySalesPerson
SET SalesQuota = 50000.00
FROM MySalesPerson m
   INNER JOIN Employee e ON e.BusinessEntityID = m.BusinessEntityID
WHERE e.JobTitle = 'Database Administrator';

This query updates the SalesQuota column in the MySalesPerson table to $50,000 for all records where the BusinessEntityID matches that of the Employee table and has a JobTitle of “Database Administrator”.

Example: Updating Multiple Columns with a SQL Query

In some cases, you may need to update multiple columns in a table using values from another table. This can be achieved by specifying multiple columns in the SET clause and joining the tables.

UPDATE MySalesPerson
SET TerritoryID = s.TerritoryID,
    SalesQuota = s.SalesQuota,
    Bonus = s.Bonus,
    CommissionPct = s.CommissionPct,
    SalesYTD = s.SalesYTD,
    SalesLastYear = s.SalesLastYear,
    ModifiedDate = GETDATE()
FROM MySalesPerson m
   INNER JOIN SalesPerson s ON s.BusinessEntityID = m.BusinessEntityID;

This query updates multiple columns in the MySalesPerson table with values from the SalesPerson table.

Best Practice: Using Transaction Control and OUTPUT Clause

When performing ad hoc data updates, it is recommended to use transaction control and the OUTPUT clause. This allows you to validate the results before committing the changes to the database.

BEGIN TRAN;
UPDATE schema.TableName
SET Column2 = 'NewValue'
OUTPUT DELETED.Column2 AS [Before],
       INSERTED.Column2 AS [After]
FROM schema.TableName
WHERE Column1 = 'Condition';
ROLLBACK; -- Rollback if the results are not as expected
-- OR
COMMIT; -- Commit if the results are as expected

In this template, you can review the before and after values using the OUTPUT clause and choose to either rollback or commit the transaction based on the expected results.

By following this best practice, you can avoid unintended changes to your data and ensure the integrity of your database.

Conclusion

Updating data in SQL Server tables is a fundamental task in database management. In this tutorial, we explored various ways to update data in SQL Server tables, including updating a single column for a single row, updating a column for all rows, updating data based on a join, and updating multiple columns with a SQL query. We also discussed the best practice of using transaction control and the OUTPUT clause when performing ad hoc updates. By following these techniques, you can efficiently update data in your SQL Server tables while maintaining data integrity.