Chapter 8: SQL Update

SQL UPDATE

The UPDATE command is used to update existing rows in a table.Which rows is to be update, it is decided by a condition. To specify condition, we use WHERE clause.

Let’s see the Syntax:

UPDATE table_name  
SET column_name = expression  
WHERE conditions 

Let’s take an example: here we are going to update an entry in the Student table.

SQL statement:

UPDATE Student  
SET Gender = 'Male'  
WHERE Student_Name = 'Dinkar Mishra' 

Student Table:

Student_NameGenderMobile_NumberHOME_TOWN
Ramesh PatelMale7503896532Lucknow
Dinkar MishraFemale9270568893Varanasi
Soham SHARMAMale9990449935Lucknow

See the result after updating value:

Student_NameGenderMobile_NumberHOME_TOWN
Ramesh PatelMale7503896532Lucknow
Dinkar MishraMale9270568893Varanasi
Soham SHARMAMale9990449935Lucknow

Updating Multiple Fields:

If you are going to update multiple fields, you should separate each field assignment with a comma.

SQL UPDATE statement for multiple fields:

UPDATE Student  
SET Gender = 'Female' HOME_TOWN = ‘Pune’
WHERE Student_Name = 'Dinkar Mishra'  

Result of the table is given below:

Student_NameGenderMobile_NumberHOME_TOWN
Ramesh PatelMale7503896532Lucknow
Dinkar MishraFemale9270568893Pune
Soham SHARMAMale9990449935Lucknow

SQL UPDATE DATE

To update a date & time field in SQL, you should use the following query.

Syntax of sql update date.

UPDATE table   
SET Column_Name = 'YYYY-MM-DD HH:MM:SS'  
WHERE Id = value  

For example:

If you want to change the first row which id is 1 then you should write the following syntax:

UPDATE table   
SET EndDate = '2014-03-16 00:00:00.000'   
WHERE Id = 1   

Note: you should always remember that SQL must attach default 00:00:00.000 automatically.

This query will change the date and time field of the first row in that above assumed table.