Update in sql server

Update in sql server

Update in sql server: After inserting records in a table. The records need to be modified. It needs to be modified for any reason. Sometimes the record(s) goes wrong,  sometimes the company policy may be changed. For example, a customer ordered a product and the product is delivered to the customer. Now, the number of item(s) must be deducted.

SQL server has a command to change/modify the existing records in a table. The statement is called UPDATE. The UPDATE statement is a very important feature in SQL server. SQL UPDATE Statement is very useful while using t-sql (Transact-SQL). In the application development world the SQL SERVER is used to store records in a table.  

SQL UPDATE Statement: Update statement is used to change data/record in table.

-UPDATE Statement is very useful for modifying existing record(s) in the table.

-UPDATE Statement can change/modify a single column. 

-UPDATE Statement can change/modify  the multiple columns.

-While updating a single column there must be unique records in a table.

-The unique records in the table may be the primary key.

SQL Update Syntax:

UPDATE table_name
SET column1 = value1, column2 = value2, ..
WHERE condition;

[NOTE: Be careful while updating record(s) in a table. While updating records should be used WHERE clause with UPDATE Statement. The WHERE clause specifies the record(s). Without the WHERE clause, all the record(s) are updated.]

Description:
          Update: SQL statement.
          SET: SQL Keyword.
          column1= Name of column,
          value1..:=New vlaue.
          Where: Sql keyword

SQL UPDATE statement example:

Create Table OldItems
(
ItemCode int NOT NULL constraint pkprimary1 Primary key,
Name Varchar(30)NOT NULL,
Category VarChar(30) NOT NULL,
Company VarChar(30)NOT NULL,
Country VarChar(30)NOT NULL,
Quantity float NOT NULL,
Price float NOT NULL,
ContactPerson VarChar(30) NULL,
Remarks VarChar(150)NULL
)

Insert into OldItems Values('001','Laptop','Electronic','DELL','USA','50','500',NULL,NULL)
Insert into OldItems Values('002','Desktop','Electronic','DELL','USA','10','300',NULL,NULL)
Insert into OldItems Values('003','RAM','Electronic','DELL','USA','50','300',NULL,NULL)
Insert into OldItems Values('004','MOBILE','Electronic','iPhone','USA','20','400',NULL,NULL)

DEMO Records for Update in sql 

Select * from OldItems

Result:

ItemCode	Name	Category	Company	Country	Quantity	Price	ContactPerson	Remarks
1	Laptop	Electronic	DELL	USA	50	500	NULL	NULL
2	Desktop	Electronic	DELL	USA	10	300	NULL	NULL
3	RAM	Electronic	DELL	USA	50	300	NULL	NULL
4	MOBILE	Electronic	iPhone	USA	20	400	NULL	NULL

SQL UPDATE statement examples:

1. SQL UPDATE Query for single column

Give below example, updates single column

For Example: 

Update OldItems Set Name='DELL Desktop' Where ItemCode='002'

Above SQL Server update example, updates the ‘Desktop’ to ‘DELL Desktop’ Where ItemCode is ‘002’

After Update Query, Record(s) will be:

Select * from OldItems

Result:

ItemCode	Name	Category	Company	Country	Quantity	Price	ContactPerson	Remarks
1	Laptop	Electronic	DELL	USA	50	500	NULL	NULL
2	DELL Desktop	Electronic	DELL	USA	10	300	NULL	NULL
3	RAM	Electronic	DELL	USA	50	300	NULL	NULL
4	MOBILE	Electronic	iPhone	USA	20	400	NULL	NULL

2. Another update single column example:

Update OldItems Set Name='DELL Desktop Server',Quantity='5' Where ItemCode='002'

Above Sql Server Update example, updates the ‘DELL Desktop’ to ‘DEL Desktop Server’ and Quantity ‘10’ to ‘5’ Where ItemCode is ‘002’

After Update Query, Record will be:

Select * from OldItems

ItemCode	Name	Category	Company	Country	Quantity	Price	ContactPerson	Remarks
1	Laptop	Electronic	DELL	USA	50	500	NULL	NULL
2	DELL Desktop Serve	Electronic	DELL	USA	5	300	NULL	NULL
3	RAM	Electronic	DELL	USA	50	300	NULL	NULL
4	MOBILE	Electronic	iPhone	USA	20	400	NULL	NULL

3. SQL UPDATING multiple columns

Update OldItems Set ContactPerson='SMITH' Where ProductCompany='DELL'

After updating, multiple columns, records will be:

Select * from OldItems

ItemCode	Name	Category	Company	Country	Quantity	Price	ContactPerson	Remarks
1	Laptop	Electronic	DELL	USA	50	500	SMITH	NULL
2	DELL Desktop Serve	Electronic	DELL	USA	5	300	SMITH	NULL
3	RAM	Electronic	DELL	USA	50	300	SMITH	NULL
4	MOBILE	Electronic	iPhone	USA	20	400	NULL	NULL

4.SQL UPDATE without WHERE clause

Update OldItems Set Remarks='Defacted

'
After updating, Records will be:

Select * from OldItems

ItemCode	Name	Category	Company	Country	Quantity	Price	ContactPerson	Remarks
1	Laptop	Electronic	DELL	USA	50	500	SMITH	Defacted
2	DELL Desktop Serve	Electronic	DELL	USA	5	300	SMITH	Defacted
3	RAM	Electronic	DELL	USA	50	300	SMITH	Defacted
4	MOBILE	Electronic	iPhone	USA	20	400	NULL	Defacted

SQL UPDATE statement in video







SQL UPDATE statement in Image

[Update Query can be used with SQL JOIN statement.]

In this sql tutorial, you have learned to use UPDATE in sql. You can change or modify the existing data in sql.