Updating Records in a SQL Database

The UPDATE statement is used to modify existing rows in a table. Upon executing this command the record values are modified based on values passed in the query. With the WHERE clause, you can update specific records from the table. Just be careful not to update records you don't intend to.

After the tables have been created and the data added, changing or updating data in the tables becomes one of the day-to-day processes in maintaining a database. Although there are multiple ways to change data in an existing SQL database, for this exercise, we are only going to focus on the "UPDATE statement" which can be used to update data in specific rows in a table or view.

UPDATE Syntax

UPDATE table
SET field = value,
field = value,
field = value
WHERE conditions;

Here are a few things you need to know about the UPDATE statement

The UPDATE statement can change data values in single rows, groups of rows, or all the rows in a table or view.
It can also be used to update rows in a remote server using either a linked server name or the OPENROWSET and OPENQUERY functions.
An UPDATE statement referencing a table or view can change the data in only one base table at a time.

Going back to the SalesLT.EntryLog table we created earlier and inserted data into the table using the INSERT statement, we are now going to change the content of a row in the table by using an UPDATE statement.

In the example below we will use the WHERE clause of the UPDATE statement to restrict the update to a single row.

How to Update Records using the UPDATE statement

1. Type and execute the following UPDATE statement to change the SalesPerson of the (EntryLogID = 3) from Woody Harrison, to Woody Harrelson.

-- Update example 1
UPDATE SalesLT.EntryLog
SET SalesPerson = 'Woody Harrelson'
WHERE EntryLogID = 3
GO

Insert into SQL database

2. Type and execute the following UPDATE statement to add a Description to SalesPerson = Jeff Gorvette, EntryLogID = 3. The description currently has a NULL value.

-- Update example 2
UPDATE SalesLT.EntryLog
Notes = 'Jeff Gorvette is one of the greatest American race cars. Donning the stars and stripes of his country's flag, the No. 24 Corvette C6.R has proven his ability to succeed on the big ovals.'
WHERE EntryLogID = 5
GO

UPDATE Statement Clauses

The UPDATE statement has these major clauses.

SET
Contains a comma-separated list of the columns to be updated and the new value for each column, in the form column_name = expression. The value supplied by the expressions include items such as constants, values selected from a column in another table or view, or values calculated by a complex expression.

FROM
Identifies the tables or views that supply the values for the expressions in the SET clause, and optional join conditions between the source tables or views.

WHERE
Specifies the search condition that defines the rows from the source tables and views that qualify to provide values to the expressions in the SET clause.

**learn update query with practical example.

This update statement increases the prices of all the Northwind products in category 2 by 10 percent:

UPDATE Northwind.dbo.Products
SET UnitPrice = UnitPrice * 1.1
WHERE CategoryID = 2

How to Change Data Using the SET Clause

SET specifies the columns to be changed and the new values for the columns. The values in the specified columns are updated with the values given in the SET in all rows that match the WHERE clause search condition. If no WHERE clause is specified, all rows are updated. For example, if all the publishing houses in the publishers table move their head offices to Atlanta, Georgia, this UPDATE statement would be used:

UPDATE publishers SET city = 'Atlanta', state = 'Georgia'

Computed column values can be calculated and used in an update. For example, to double all the prices in the titles table, the price column in the titles table can be set to equal price * 2.

The expressions used in the SET clause can also be subqueries that return only one value; for example, if the Northwind database had an OrderSummary table:

SQL

https://learn.microsoft.com/en-us/sql/t-sql/queries/update-transact-sql?view=sql-server-ver16 //////////////////////////////////////////

How to Change Data Using the WHERE Clause

To modify limited records in the database use WHERE clause along with UPDATE command. The following example uses the WHERE clause to specify which rows to update. The statement updates the value in the CompanyName column of the Northwind.dbo.Shippers table for all rows that have an existing value of 'United Packages' in the CompanyName column and have a value in the Name column that starts with 'United Shippers'.

The WHERE clause performs two functions:
Specifies the rows to be updated.
Indicates the rows from the source tables that qualify to supply values for the update if a FROM clause is also specified.

This UPDATE statement implements a name change for one of the shippers:

USE Northwind; GO UPDATE Northwind.dbo.Shippers
SET CompanyName = 'United Shippers'
WHERE CompanyName = 'United Packages' GO

Note: If no WHERE clause is specified all rows in the table are updated.

How to Change Data Using the FROM Clause

Use the FROM clause to pull data from one or more tables or views into the table to update. For example, when author Dirk Stringer gets a contract, a title identification number is assigned to his book, The Psychology of Computer Cooking, in the titles table. Dirk’s row in the titleauthor table can be updated by adding a title identification number for this latest book.

This example updates Dirk Stringer’s row in the titleauthor table to add a title identification number for his latest book, entitled The Psychology of Computer Cooking:

SQL

Note

Updating Records in a SQL Database Table (Done)