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.