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.
1,410 total views, 1 views today