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 .

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 in all rows in a table for those column.

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 a condition in update statement.

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 where 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.

Also Read..

Update from Select statement 

SQL WHERE Clause