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]
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.
UPDATE students SET User_Name = 'beinghuman' WHERE Student_Id = '3'
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'
|Student_Id||First Name||Last Name||User_Name|
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.
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:
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:
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:
We can see that these records have been updated. We can also update the multiple records in single column
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;
Here, we can see that these records have been updated.