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_nameIt 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 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;
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: 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 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;
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.
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.
