SQL Update Statement

SQL Update Statement: In this SQL tutorial’s previous chapters, we explored how to INSERT Data into a table, Also, we have learned how to select data from a database table depending on various conditions. Now let’s focus our attention on another important task: updating existing records in a database table.

We use databases to store data in tables. With the SQL language, we can utilize an UPDATE statement when it is necessary to make edits or changes to specific fields within the stored data during its lifetime. This action query allows us to update multiple records simultaneously with one command.

Introduction To SQL Update

The UPDATE command in SQL is a great way to modify or change the existing records within a table. If you want to update just one particular value of a record, it can be done by using the WHERE clause and the UPDATE clause.

Please note that if you have not used the WHERE clause with the update statement, all rows will be affected. Additionally, depending on your requirements, you have an option of updating either a single or several columns at once through the utilization of an UPDATE statement.

Syntax Of the SQL Update Statement

The update command requires a table Name and a set keyword for updating records within the table. Furthermore, with the help of the ‘while’ clause, it’s possible to make multiple changes simultaneously!

UPDATE table_name
SET col1=val1, col2=val2…
[Where condition];
  • table_name It is the table name in which you want to update the data.
  • col1, col2, … are the names of the columns you want to update.
  • val1, val2, … are the new values you want to set for the corresponding columns.
  • WHERE – It is an optional clause that specifies which records to update. If you omit the WHERE clause, all records in the table will be updated with the new values.

The WHERE clause in the UPDATE statement allows you to specify which record or records should be updated. If this clause is omitted, all available records will be changed.

SQL Update Query One Column Example

In this example, we will use the EMPLOYEE table, and inside the table, we will try to update the employee name whose employee number is 3.

UPDATE EMPLOYEE 
SET ENAME = 'SAN JOSE'
WHERE EMPNO = 3;
SQL Update Statement 1

SQL Update Multiple Columns

By following the below example, we can update sql update multiple columns.

UPDATE EMPLOYEE 
SET ENAME = 'JOSE', MGR=7, DEPTNAME='SERVICES'
WHERE EMPNO = 3;
SQL Update Statement 2

If you need to raise the salaries of all employees by 10% in the Employee table, then one UPDATE statement should do the trick.

UPDATE EMPLOYEE
SET SAL = SAL + ( SAL * 10/100);
SQL Update Statement 3

SQL Update: Omitting WHERE clause

Above we have seen how to update a specific records, but before thatwe have to select that specific record and for that we have used the WHERE clause. But if any chance if you forget to mention the where clause then it will update all the records.

To get an better understand, lets take an example:

UPDATE EMPLOYEE
SET ENAME='Suraaj';
SQL Update Statement 4

SQL Update Set: Sub Query

If there is a requirement for update the salary of the employee whose employee ID is 5, then we can achive this with the help of the sub query and here is the example:

UPDATE EMPLOYEE
SET SAL = (SELECT MAX(SAL) FROM EMPLOYEE)
WHERE EMPNO=5;
SQL Update Statement 5

The WHERE clause should be placed outside the brackets to apply to the UPDATE statement instead of the SELECT statement.

SQL Update Value – Management Studio

We can also update the records of a table with the help of Management studio and to update the same you have to right-click on the Table, and Select Script Table as -> UpdateTo -> New Query Editor Window.

SQL Server Management Studio

When you click over the New Query Editor Window, a statement query will be created that displays all of the column names and data types it will accept. All you needs to be done is for <> to be switched out with your desired value as well as any changes made in terms of where conditions. The Management Studio code generated from this looks like:

USE [Practice]
GO

UPDATE [dbo].[EMPLOYEE]
   SET [EMPNO] = <EMPNO, int,>
      ,[ENAME] = <ENAME, varchar(10),>
      ,[MGR] = <MGR, int,>
      ,[HIREDATE] = <HIREDATE, date,>
      ,[SAL] = <SAL, decimal(7,2),>
      ,[DEPTNAME] = <DEPTNAME, varchar(10),>
      ,[DEPTID] = <DEPTID, int,>
 WHERE <Search Conditions,,>
GO

Here I want to update the Employee Number 1 salary to 10000. so the script will looks like below

UPDATE  [dbo].[EMPLOYEE]
SET [SAL]=10000
WHERE EMPNO=1;
SELECT * FROM EMPLOYEE;

Conclusion:

In this tutorial, you’ve been shown how to use the SQL Server UPDATE statement for effectively modifying existing data within a table. If still have any questions then you can ask us in the comment section.

Avatar for Softwaretestingo Editorial Board

I love open-source technologies and am very passionate about software development. I like to share my knowledge with others, especially on technology that's why I have given all the examples as simple as possible to understand for beginners. All the code posted on my blog is developed, compiled, and tested in my development environment. If you find any mistakes or bugs, Please drop an email to softwaretestingo.com@gmail.com, or You can join me on Linkedin.

Leave a Comment