SQL UPDATE Statement

The SQL Server UPDATE Statement is used to replace or change the existing records with new data. The syntax of the SQL Update Statement is as shown below.

UPDATE [Table_Name] 
SET [Column1] = [Value1], 
    [Column2] = [Value2],
    [ColumnN] = [ValueN]
WHERE Condition

• Column1…ColumnN: Column names on which you want to change.
• Value1…ValueN: Provide the new Values of the columns.
• Condition: Here, we have to provide filters or conditions. If the condition is TRUE, then only the SQL Server statement will update the records.

We are going to perform the different types of update operations on this SQL Server table.

SQL Server UPDATE Statement Example

Let us see how to write this statement. We are going to use the query to update the Yearly Income of all the Customers.

UPDATE [Cust]
SET [YearlyIncome] = 100000

The above query will replace the yearly income of all the Employees to 100000

Here, we haven’t specified the WHERE condition. That’s why the Server changed all 15 records. It is the most critical approach in the real-time environment because if you forget the where clause, you are ruing the client data.

In this SQL update statement example, we replace one single column or record.

UPDATE [Cust]
SET [YearlyIncome] = 250000
WHERE [EmpId] = 5

See, it has the [Yearly Income] with 250000 for the Employee whose ID value = 5

SQL Server UPDATE Multiple columns

In this example, we are going to use multiple columns or records.

UPDATE [Cust]
SET [YearlyIncome] = 123456,
    [Sales] = 4500
WHERE [Occupation] = 'Professional'

As you see from the below image, the SQL update statement changes the [Yearly Income] with 123456 and the sale as 4500 for all the records whose Occupation is Professional.

SQL Server Update With New values

Suppose the government has increased some Tax values to 0.5% for all Goods and Services (or any particular Good). Then we can use this statement to change those values with a new one. OR you want to increase the prices of your goods then you can use it.

UPDATE [Cust]
SET [Sales] = [Sales] * 2

How to update from another SQL table?

In this example, we show how to update a table using records from another table. Here, we refreshed the record in one table with the data from another Table.

UPDATE [Cust]
SET [YearlyIncome] = ( 
			SELECT [YearlyIncome] FROM [Employee] AS Tab1
			WHERE [Cust].[FirstName] = [Tab1].[FirstName]
			AND 
			[Cust].[LastName] = [Tab1].[LastName])

Messages
--------
(14 row(s) affected)

Let me see the data

SQL Update Stored Procedure

In this example, we use this statement inside a Stored procedure. We changed the Last Name to Tutorial Gateway and Occupation as Admin for all the records present in the table whose Yearly Income is greater than or equal to 80000.

IF OBJECT_ID ( 'sp_Example', 'P' ) IS NOT NULL   
    DROP PROCEDURE sp_Example;  
GO

CREATE PROCEDURE sp_Example
AS
BEGIN
	SET NOCOUNT ON;
	UPDATE [Cust] SET [LastName] = N'Tutorial Gateway',
					 [Occupation] = N'Admin'
	WHERE [YearlyIncome] >= 80000
	 
END
GO

Messages
-------
Command(s) completed successfully.

Let me use EXEC Command (Execute Command) to execute the stored procedure

EXEC dbo.sp_Example

GO

Messages
-------
Command(s) completed successfully.

Now, let us see whether the execution of the stored procedure altered the Last Name and Occupation or not

SELECT [EmpID], [FirstName], [LastName], [Education]
 ,[Occupation], [YearlyIncome], [Sales]
  FROM [Cust]

Update Statement from SQL Server Management Studio

If you can access the Management Studio, use Intellisense to generate the UPDATE Statement. To do so, right-click on the Table, and Select Script Table as -> UpdateTo -> New Query Editor Window

Once you select the New Query Editor Window option, it will generate the below-shown statement query. As you can see, it displays all the column names and the data type it will accept. All you have to do is, replace <> with your required value and modify the where condition. And the Code generated by the Management Studio is:

UPDATE [dbo].[Cust]
   SET [FirstName] = <FirstName, nvarchar(255),>
      ,[LastName] = <LastName, nvarchar(255),>
      ,[Education] = <Education, nvarchar(255),>
      ,[Occupation] = <Occupation, nvarchar(255),>
      ,[YearlyIncome] = <YearlyIncome, float,>
      ,[Sales] = <Sales, float,>
 WHERE <Search Conditions,,>
GO

For the demonstration purpose, let me add 100000 to the employee’s yearly income column.

SET [YearlyIncome] = [YearlyIncome] + 100000

Messages
--------
(14 row(s) affected)

Let me show you the modified information or records.