SQL: UPDATE Query

What is an UPDATE QUERY?

The SQL UPDATE statement is used to modify the data in tables’ records. A condition determines which rows are to be updated. The WHERE clause is used to specify conditions.

Syntax:

UPDATE table_name  
SET column_name = expression  
WHERE condition

where,

• table_name: name of the table
• column_name: name of first, second, third column…
• expression: new value for first, second, third column…
• condition: condition to select the rows for which the values of columns need to be updated.

Example:

Here we have a Student table.

Student_Id	FirstName	LastName	Course
12	Ada	Sharma	BCA
22	Rahul	Maurya	B.Des
30	James	Walker	M.Tech

• Updating single value:

Query for updating only a single field in the Student table based on Student_Id.

UPDATE Student  
SET LastName = 'Kumar'  
WHERE Student_Id = '22'  

Output:

Student_Id	FirstName	LastName	Course
12	Ada	Sharma	BCA
22	Rahul	Kumar	B.Des
30	James	Walker	M.Tech

• Updating multiple values:

Query for updating multiple values in a single statement based on the Student_Id.

UPDATE Student
SET FirstName='Adiba', COURSE='BBA' 
where Student_Id=12; 

Output:

Student_Id	FirstName	LastName	Course
12	Adiba	Sharma	BBA
22	Rahul	Kumar	B.Des
30	James	Walker	M.Tech

• Omitting WHERE clause: 

If we leave out the WHERE clause in the update query, all of the rows will be updated.

UPDATE Student  
SET Course = 'MBA'  

Output:

Student_Id	FirstName	LastName	Course
12	Adiba	Sharma	MBA
22	Rahul	Kumar	MBA
30	James	Walker	MBA

As we can notice, Course field for all three students is updated with “MBA”, due to the omission of where clause.

Incrementing Integer Value using UPDATE Query

To increment an integer value with the UPDATE command, we use the SET clause with the + operator to add the desired value to the column’s existing value. Here’s an illustration:

Sr.	Item	Quantity
1	Candles	89
2	Balls	230
3	Lights	122

UPDATE Product SET Quantity = Quantity+10; 

After executing this query the quantity of all the items will get incremented by 10.

Note: also read about SQL: INSERT Query

Follow Me

Please follow me to read my latest post on programming and technology if you like my post.

https://www.instagram.com/coderz.py/

https://www.facebook.com/coderz.py