SQL UPDATE Statement 11

SQL UPDATE Statement

by

Let us see how to write the SQL Server UPDATE statement. The SQL UPDATE Statement is used to update the existing records with new data. The syntax of the SQL Server update statement is

-- SQL Server UPDATE Syntax
UPDATE [Table_Name] 
SET [Column1] = [Value1], 
    [Column2] = [Value2],
    [ColumnN] = [ValueN]
WHERE Condition
  • Column1…ColumnN: Please select the column names on which you want to update.
  • Value1…ValueN: Please specify the values that you want to update.
  • Condition: Here, we have to provide filters or conditions. If the condition is TRUE, then only the SQL Server update statement will update the records.

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

SQL UPDATE Statement 1

SQL UPDATE Statement 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

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

SQL UPDATE Statement 2

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

SQL UPDATE Statement to Update Single record

In this example, we update one single record.

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

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

SQL UPDATE Statement 3

SQL UPDATE 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'

As you see, the update statement updated the [Yearly Income] with 123456 and sale as 4500 for all the records whose Occupation is Professional

SQL UPDATE Statement 4

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

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

How to update from another table

In this SQL Server update statement example, we show how to update a table using records from another table. Here, we 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])
Messages
--------
(14 row(s) affected)

Let me see the data

SQL UPDATE Statement 6

SQL Server Update Stored Procedure

In this example, we use the SQL Server Update Statement inside a Stored procedure. We 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 80000.

-- 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
Messages
-------
Command(s) completed successfully.

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
Messages
-------
Command(s) completed successfully.

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

-- Sql Server update statement example
USE [SQL Tutorial]
GO
SELECT [EmpID], [FirstName], [LastName], [Education]
 ,[Occupation], [YearlyIncome], [Sales]
  FROM [SQL Update]
SQL UPDATE Statement 10

Update Statement from Management Studio

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

SQL UPDATE Statement 11

Once you selected the Update To New Query Editor Window, it will generate the below-shown Sql Server update statement 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 generated by the Management Studio is:

-- Update Statement in Sql Server example
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 Update statement 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
Messages
--------
(14 row(s) affected)

Let me show you the Updated information or records.

SQL UPDATE Statement 14