SQL UPDATE Statement 6

SQL UPDATE Statement

The SQL UPDATE Statement is used to replace or change the existing records with new data. The syntax of the SQL Server update statement 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 update statement will change the records.

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

Customer Records

SQL UPDATE Statement Example

Let us see how to write the SQL UPDATE statement. We are going to change the Yearly Income of all the Customers.

UPDATE [Cust]
SET [YearlyIncome] = 100000

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

SQL UPDATE Statement 2

Here, we haven’t specified 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 forgot 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

SQL UPDATE Multiple columns

In this example, we are going to change the 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 replaced the [Yearly Income] with 123456 and sale as 4500 for all the records whose Occupation is Professional.

SQL UPDATE Statement 4

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

How to update from another table in SQL?

In this SQL Server update statement example, we show how to change 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 from another Table

SQL Server 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 changed the Last Name and Occupation or not

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

SQL Update Statement from Management Studio

If you can access the Management Studio, use the Intellisense to generate the 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 selected the New Query Editor Window option, it will generate the below-shown 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 [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 employees yearly income column.

SET [YearlyIncome] = [YearlyIncome] + 100000
Messages
--------
(14 row(s) affected)

Let me show you the changed information or records.

SQL UPDATE Statement 14