SQL SERVER UPDATE

SQL UPDATE Statement is used to modify an existing records in a table.

SYNTAX

UPDATE table_name SET column1 = newvalue1, column2 = newvalue2,..
WHERE condition

Lets look at an example of UPDATE statement in SQL SERVER.

First we create a new table named as SALES and insert records into table, following are scripts.

CREATE TABLE dbo.SALES
(SALES_ID INT NOT NULL, 
ITEM VARCHAR(50), 
ITEM_CODE VARCHAR(15),
ITEM_QTY INT,
PURCHASED_DATE DATE
)

INSERT INTO dbo.SALES
(SALES_ID, ITEM, ITEM_CODE, ITEM_QTY, PURCHASED_DATE)
VALUES( 101, 'LAPTOP- HP','PAVILLION-XCV89', 500,'2019-04-05'),
( 102, 'KEYBOARD','KEYB-567', 100,'2019-05-19'),
( 103, 'MOUSE','MM-ER56', 250,'2019-05-15'),
( 104, 'PEN DRIVE','PD-20ET', 400,'2019-07-25'),
( 105, 'RADEON GRAPHIC CARD',null, 478,'2019-09-19')

Now, we have a table sales in SQL Server database shown below.

SELECT * FROM dbo.SALES

Update records in a table using update statement

Lets start with updating a null value, as you can see in a table Sales there is a null value for an ITEM_CODE for SALES_ID value 105.

Following update statement, updates an ITEM_CODE value to ‘GRPH-456’ for SALES_ID 105.

UPDATE dbo.SALES SET ITEM_CODE ='GRPH-456' WHERE SALES_ID =105

Lets check the record in a table for SALES_ID =105, and you can see a new value is updated in place of null for ITEM_CODE for SALES_ID 105.

SELECT * FROM dbo.SALES WHERE SALES_ID =105

Update values for multiple columns using Update Statement

Following statement updates a new values for column ITEM_CODE and ITEM_QTY for SALES_ID =104

UPDATE dbo.SALES SET ITEM_CODE ='PD-21ET', ITEM_QTY = 350 WHERE SALES_ID =104

You can see, a new values for ITEM_CODE and ITEM_QTY are updated for SALES_ID 104.

SELECT * FROM dbo.SALES WHERE SALES_ID =104

What happens if you do not specify condition in WHERE Clause with UPDATE statement ?

If you do not specify WHERE condition in UPDATE statement, it updates a same values for those column in all rows in a table.

So you have to be very careful while using update statement, make sure you provide a proper condition that update a specific records that you want to update in a table.

Lets take an example, Suppose you want to update a new value ‘PD-456’ for column ITEM_CODE For SALES_ID =104 but by mistake you forgot to provide condition in update statement.

You can see, in following update statement, we have not provided any where condition purposely.

 UPDATE dbo.SALES SET ITEM_CODE ='PD-456'

As you can see, without specifying a condition in UPDATE statement, an ITEM_CODE value ‘PD-456’ is updated for each rows in the table.

SELECT * FROM dbo.SALES

As you can see, same value ‘PD-456’ is updated for all records in the table.

Update from Select statement 

SQL WHERE Clause

 1,410 total views,  1 views today