coderz.py

Keep Coding Keep Cheering!

SQL: UPDATE Query

SQL Views

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_IdFirstNameLastNameCourse
12AdaSharmaBCA
22RahulMauryaB.Des
30JamesWalkerM.Tech
Student
  • 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_IdFirstNameLastNameCourse
12AdaSharmaBCA
22RahulKumarB.Des
30JamesWalkerM.Tech
Student
  • 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_IdFirstNameLastNameCourse
12AdibaSharmaBBA
22RahulKumarB.Des
30JamesWalkerM.Tech
Student
  • 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_IdFirstNameLastNameCourse
12AdibaSharmaMBA
22RahulKumarMBA
30JamesWalkerMBA
Student

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.ItemQuantity
1Candles89
2Balls230
3Lights122
Product
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

Leave a Comment

Your email address will not be published. Required fields are marked *

Advertisement