Update Query in SQL
Update is an SQL command that is used to modify the data that in already present in database. Update is a command of DML. DML means Data Manipulation Language. Basically, the update command is used to update the data which is present in the table. We can update the single column as well as multiple columns.
In other word, we can say that it is basically used to change the data which is previously present in database. The update command can be returned in the folllowing format:
UPDATE table_name SET [column_name1= value1,... column_nameN = valueN] [WHERE condition]
Syntax:
UPDATE table_name
SET col_name = Value
WHERE Conditon;
With the help of this command, we can update the data inside the table. The UPDATE statement helps to let the database system know that we wish to update the database present in table in specific table name_parameter. The column we want to update are listed after the SET keyword and assigned with new updated values. We can separate the column with the help of commas. With the help of WHERE clause, we can mention the column or rows, we want to update.
Example:
UPDATE students
SET User_Name = 'beinghuman'
WHERE Student_Id = '3'
Table:
Student_Id | FirstName | LastName | User_Name |
1 | Ada | Sharma | sharmili |
2 | Rahul | Maurya | so famous |
3 | James | Walker | jonny |
Output:
Student_Id | FirstName | LastName | User_Name |
1 | Ada | Sharma | sharmili |
2 | Rahul | Maurya | sofamous |
3 | James | Walker | beinghuman |
Updating Multiple Data
In update query, we can update multiple values, if we want to update multiple values, we need to separate field assignments with a comma.
SQL command, for multiple fields:
UPDATE students
SET User_Name = 'beserious', First_Name = 'Johnny'
WHERE Student_Id = '3'
Output:
Student_Id | First Name | Last Name | User_Name |
1 | Ada | Sharma | sharmili |
2 | Rahul | Maurya | sofamous |
3 | Johnny | Walker | be serious |
Syntax of MySQL:
UPDATE table_name
SET field_1= new value 1, field_2= new value 2,
WHERE Clause
SQL UPDATE with SELECT Command
In SQL, we can update the specific value or row with the help of select statement. In other words, we can use select command to update records through UPDATE statement.
Syntax:
UPDATE table Destination
SET table Destination.col = value
WHERE EXISTS (
SELECT col2.value
FROM tblSource
WHERE tblSource.join_col = tblDestination. Join_col
AND tblSource.Constraint = value)
Or you can try this one:
UPDATE
Table
SET
Table.column1 = othertable.column 1,
Table.column2 = othertable.column 2
FROM
Table
INNER JOIN
Other_table
ON
Table.id = other_table.id
SQL UPDATE Column
In SQL, we can also update the column and we can update the single or multiple columns with the help of update statement.
Let’s take an Example, we have an employee named table as shown below:
Employee ID | Name | City | Salary |
1 | Tom | Delhi | 25000 |
2 | Harry | Delhi | 40000 |
3 | Hannah | Bangalore | 50000 |
4 | Sneha | Mumbai | 20000 |
5 | Arun | Kolkata | 28000 |
6 | Vanya | NULL | 35000 |
Before updating, first we need to check all the data types of all column because it is very important. Additionally, if any column has constraint then we need to update the values according to constraints.
We can check that with the help of DESC statement:
Field | Type | Null | Key | Default |
Employee ID | int | no | primary | null |
Name | Varchar(255) | no | null | |
City | Varchar(255) | yes | null | |
Salary | int | yes | null |
As you can see, our table has primary key constraint in employee ID column, so it is very important that every values of employee ID should be unique and not null. For example, if we change the address of Harry, we have to update the city attribute of our table.
UPDATE Employee
SET city='Chennai'
WHERE Employee ID =2;
After the execution, the query above will create the following table:
Output:
Employee ID | Name | City | Salary |
1 | Tom | Delhi | 25000 |
2 | Harry | Chennai | 40000 |
3 | Hannah | Bangalore | 50000 |
4 | Sneha | Mumbai | 20000 |
5 | Arun | Kolkata | 28000 |
6 | Vanya | NULL | 35000 |
We can see that these records have been updated. We can also update the multiple records in single column
Example:
If we want to update the employee salary less than 30000 then we will use the following query:
UPDATE Employee
SET salary= 30000
WHERE salary<30000;
Output:
Employee ID | Name | City | Salary |
1 | Tom | Delhi | 30000 |
2 | Harry | Chennai | 40000 |
3 | Hannah | Bangalore | 50000 |
4 | Sneha | Mumbai | 30000 |
5 | Arun | Kolkata | 30000 |
6 | Vanya | NULL | 35000 |
Here, we can see that these records have been updated.