SQL Tutorial

SQL Tutorial SQL Introduction SQL Syntax SQL Data Types SQL OPERATORS SQL COMMANDS SQL Queries

SQL Database

SQL Create Database SQL DROP Database SQL SELECT Database

SQL Table

SQL TABLE SQL CREATE TABLE SQL COPY TABLE SQL ALTER TABLE SQL DELETE SQL TRUNCATE TABLE SQL DROP TABLE SQL UPDATE TABLE SQL INSERT TABLE

SQL SELECT

SQL SELECT Statement SQL SELECT WHERE Clause SQL SELECT IN Operator SQL BETWEEN Operator SQL SELECT BETWEEN Operator SQL SELECT AND Operator SQL SELECT OR Operator SQL SELECT LIKE Operator SQL SELECT DISTINCT SQL SELECT SUM SQL SELECT MAX SQL SELECT MIN SQL SELECT AVG

SQL Clause

SQL WHERE Clause SQL GROUP BY CLAUSE SQL ORDER BY Clause SQL HAVING Clause

SQL INSERT

SQL INSERT Statement SQL INSERT INTO Statement SQL INSERT INTO Values SQL INSERT INTO SELECT SQL Insert multiple rows

SQL JOIN

SQL JOIN SQL Inner Join SQL Left Join SQL Right Join SQL Full Join SQL CROSS Join

SQL OPERATOR

SQL Comparison SQL LOGICAL Operator SQL Cast Operator SQL Arithmetic

Difference

SQL vs NOSQL WHERE vs HAVING DELETE vs DROP GROUP BY vs ORDER BY DROP vs TRUNCATE SQL IN vs SQL EXISTS Difference between Delete, Drop and Truncate in SQL

MISC

SQL SubQuery SQL CASE Commit and Rollback in SQL Pattern Matching in SQL DDL Commands in SQL DML Commands in SQL Types of SQL Commands SQL COUNT SQL Primary Key SQL FOREIGN KEY SET Operators in SQL Check Constraint in SQL SQL EXCEPT SQL VIEW SQL WHERE Statement SQL CRUD Operation Where Condition in SQL TCL Commands in SQL Types of SQL JOINS SQL Nth Highest Salary SQL NOT OPERATOR SQL UNION ALL SQL INTERSECT SQL Data Definition Language SQL Data Manipulation Language SQL Data Control Language SQL CONSTRAINTS SQL Aggregate Operators SQL KEYS Codd’s Rules in SQL What is SQL Injection? Trigger In SQL SQL WHERE Multiple Conditions Truncate function in SQL SQL Formatter WEB SQL SQL Auto Increment Save Point in SQL space() function in SQL SQL Aggregate Functions SQL Topological Sorting SQL Injection SQL Cloning Tables SQL Aliases SQL Handling Duplicate Update Query in SQL Grant Command in SQL SQL SET Keyword SQL Order BY LIMIT SQL Order BY RANDOM

How To

How to use the BETWEEN operator in SQL How To Use INNER JOIN In SQL How to use LIKE in SQL How to use HAVING Clause in SQL How to use GROUP BY Clause in SQL How To Remove Duplicates In SQL How To Delete A Row In SQL How to add column in table in SQL ? How to drop a column in SQL? How to create a database in SQL? How to use COUNT in SQL? How to Create Temporary Table in SQL? How to Add Foreign Key in SQL? How to Add Comments in SQL? How To Use Group By Clause In SQL How To Use Having Clause In SQL How To Delete Column In Table How To Compare Date In SQL How index works in SQL How to calculate age from Date of Birth in SQL How to Rename Column name in SQL what-are-single-row-and-multiple-row-subqueries">What are single row and multiple row subqueries?

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_IdFirstNameLastNameUser_Name
1AdaSharmasharmili
2RahulMauryaso famous
3JamesWalkerjonny

Output:

Student_IdFirstNameLastNameUser_Name
1AdaSharmasharmili
2RahulMauryasofamous
3JamesWalkerbeinghuman

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_IdFirst NameLast NameUser_Name
1AdaSharmasharmili
2RahulMauryasofamous
3JohnnyWalkerbe 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 IDNameCitySalary
1TomDelhi25000
2HarryDelhi40000
3HannahBangalore50000
4SnehaMumbai20000
5ArunKolkata28000
6VanyaNULL35000

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:

FieldTypeNull    Key Default
Employee IDintno primarynull
NameVarchar(255)no null
CityVarchar(255)yes null
Salaryintyes 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 IDNameCitySalary
1TomDelhi25000
2HarryChennai40000
3HannahBangalore50000
4SnehaMumbai20000
5ArunKolkata28000
6VanyaNULL35000

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 IDNameCitySalary
1TomDelhi30000
2HarryChennai40000
3HannahBangalore50000
4SnehaMumbai30000
5ArunKolkata30000
6VanyaNULL35000

Here, we can see that these records have been updated.