You are here
Home > Sql server > Sql server 2016 > Update Statement

Update Statement

UPDATE STATEMENT

In this post we will discuss about update statement in SQL server 2016.

UPDATE:

  • SQL support the update statement to allow us to modify or change the existing data in a tables.
  • The update statement in SQL either update all the rows from a table or a selected set of rows from a table.

Syntax:

UPDATE  <table_name>

SET <col_name>=<exp1>,

……..

<col n>=<exp n>

Where <predicate>;

  • If you want to change a subset rows, use a where clause with a predicate. Rows for which predicate evaluates to false or unknown are not affected.
  • An update without a where clause affects all rows.
  • Using set we can assign value to target columns.
  • SQL allow as to perform update with a variable. This feature is useful if we need to modify a row and also collect the result of the modified columns into variable.

Example:

Table: person

pid fname lname city Salary
1 Riya patel vvnagar 20000
2 Nikunj patel vvnagar 10000
3 Chirag Joshi anand 15000
4 Karan Shah nadiad 80000
5 Anika Panchal bakrol 50000
6 Jahnvi Joshi anand 40000

change the fname where pid=4

update person set fname=’shivaay’ where pid=4;

select * from person;

output:

pid fname lname city Salary
1 Riya patel vvnagar 20000
2 Nikunj patel vvnagar 10000
3 Chirag Joshi anand 15000
4 Shivaay Shah nadiad 80000
5 Anika Panchal bakrol 50000
6 Jahnvi Joshi anand 40000

 change the fname, lname, city where pid=6

update person set fname=’Amisha’, lname=’patel’, city=’London’ where pid=6;

select * from person;

output:

pid fname lname city Salary
1 Riya patel vvnagar 20000
2 Nikunj patel vvnagar 10000
3 Chirag Joshi anand 15000
4 Shivaay Shah nadiad 80000
5 Anika Panchal bakrol 50000
6 Amisha Patel London 40000

increment the salary of person whose pid=1 by 10,000

update person set salary+=10000 where pid=1;

select * from person;

output:

Id fname lname city Salary
1 Riya patel vvnagar 30000
2 Nikunj patel vvnagar 10000
3 Chirag Joshi anand 15000
4 Shivaay Shah nadiad 80000
5 Anika Panchal bakrol 50000
6 Amisha Patel London 40000

 update with a variable

declare @newsalary money;

update person set @newsalary=salary+=10000 where pid=4;

select @newsalary;

output:

90000.00

 

Keep Visiting TechwithR.com for more upcoming top technical articles.

Detail course about DBA visit TechNet Consultancy

You can also by SQL 70-761 book write by me from Amazon.
You can also by Kotlin Book write by me from Amazon.

 

 

3 thoughts on “Update Statement

Leave a Reply

Top