SQL UPDATE Statement 6

SQL UPDATE Statement

The SQL Server UPDATE Statement is used to replace or change the existing records with new data, and its syntax is

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 table.

Customer Records

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

Without Where Clause 2

Here, we haven’t specified the WHERE condition. That’s why the Server changed all the 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 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 UPDATE Statement 3

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 statement updated the [Yearly Income] with 123456 and sale as 4500 for all the records whose Occupation is Professional.

Multiple Columns

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
With New Values

How to update from another 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 Server Update From Another Table

Update Stored Procedure

In this example, we use this statement inside a Stored procedure. We change the Last Name as 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]
Stored Procedure

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

Generate Script using Management Studio

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.

Change the Income Column