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