SQL UPDATE Statement

The SQL UPDATE Statement is used to update the existing records with new data. Before we get into the practical example, let us see the syntax behind this SQL server update.

SQL UPDATE Statement Syntax

The basic syntax of the SQL Server update statement is as shown below:

-- SQL Server UPDATE Syntax
UPDATE [Table] 
SET [Column1] = [Value1], 
    [Column2] = [Value2],
    [ColumnN] = [ValueN]
WHERE Condition

• Table: Please provide the fully qualifies Table name on which you want to perform update operations (updating records)
• Column1…ColumnN: Please select the column names on which you want to update. It may be One or more.
• Value1…ValueN: Please specify the values that you want to update.
• Condition: Here we have to provide the filters or conditions. If the condition is TRUE then only UPDATE Statement will update the records.

In this article, we will show you, How to write UPDATE query in SQL Server. From the below screenshot you can observe that We have [SQL Update] table in [SQL Tutorial] and it holds 14 records. Here, we are going to perform the different types of update operations on this table

SQL UPDATE Statement

In this example, We are going to update the Yearly Income of all the Employees in [SQL Update] Table.

-- Example for SQL Server Update Statement
UPDATE [SQL Update]
SET [YearlyIncome] = 100000

OUTPUT

Above query will update the yearly income of all the Employees to 100000

NOTE: Here, we haven’t specified WHERE condition, that’s why SQL server updated all the 15 records. This is the most dangerous approach in the real-time environment because if you forgot the where clause, you are ruing the client data.

SQL UPDATE Statement – Updating Single record

In this example, We are going to update one single record in [SQL Update] Table.

-- Example for Update Query in SQL Server
UPDATE [SQL Update]
SET [YearlyIncome] = 250000
WHERE [EmpId] = 5

OUTPUT

From the below screenshot you can observe that it is updating the [Yearly Income] with 250000 for the Employee whose ID value = 5

SQL UPDATE Statement – Updating Multiple records

In this example, We are going to update multiple records into [SQL Update] Table.

-- Example for Update Query in SQL Server
UPDATE [SQL Update]
SET [YearlyIncome] = 123456,
    [Sales] = 4500
WHERE [Occupation] = 'Professional'

OUTPUT

From the below screenshot you can observe that it is updating the [Yearly Income] with 123456 and sale as 4500 for all the records whose Occupation is Professional

SQL UPDATE Statement – Updating 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 SQL Server Update statement to update those values with a new one. OR you want to increase your goods prices then you can use it.

-- Example for Update Query in SQL Server
UPDATE [SQL Update]
SET [Sales] = [Sales] * 2

OUTPUT

SQL Server UPDATE Statement – Updating records from another table

In this example, We are going to update the record in [SQL Update] Table with the new values in a [SQL Updates] Table.

-- Example for Update Query in SQL Server
UPDATE [SQL Update]
SET [YearlyIncome] = ( 
			SELECT [YearlyIncome] FROM [SQL Updates]
			WHERE [SQL Update].[FirstName] = [SQL Updates].[FirstName]
			AND 
			[SQL Update].[LastName] = [SQL Updates].[LastName])

OUTPUT

Let me see the data
OUTPUT

SQL Server Update Stored Procedure

In this example, we will show you, How to use the SQL Server Update Statement inside a Stored procedure

From the below code snippet you can see that, it will update the Last Name as Tutorial Gateway, and Occupation as Admin for all the records present in the Update table whose Yearly Income is greater than or equal to 70000.

-- Example for SQL UPDATE Stored Procedure
USE [SQL Tutorial]
GO

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

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

OUTPUT

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

-- Example for SQL UPDATE Stored Procedure
USE [SQL Tutorial]
GO

EXEC dbo.sp_UpdateEmployees

GO

OUTPUT

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

USE [SQL Tutorial]
GO
SELECT [EmpID], [FirstName], [LastName], [Education]
		,[Occupation], [YearlyIncome], [Sales]
  FROM [SQL Update]

OUTPUT

SQL Update Statement – SQL Server Management Studio

If you can access the SQL Server Management Studio then you can use the Intellisense to generate the UPDATE Statement. To do so, first Navigate to the required Database and then select the required Table. Right Click on the Table will open the Context Menu with different options

Select Script Table as -> Update To -> New Query Editor Window

Once you selected the Update To New Query Editor Window, SQL will generate the below-shown query.

As you can see, it is displaying all the column names and the data type that it will accept. All you have to do is, replace <> with your required value, and change the where condition.

and the Code that was generated by the Management Studio is:

USE [SQL Tutorial]
GO

UPDATE [dbo].[SQL Update]
   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 employees yearly income column.

-- SQL Server UPDATE Example
USE [SQL Tutorial]
GO

UPDATE [dbo].[SQL Update]
   SET [YearlyIncome] = [YearlyIncome] + 100000
GO

OUTPUT

Let me show you the Updated information or records