SQL Server update statement is used used to modify an existing records in a table, you can also update records in table based on some specific conditions.
Following is the basic syntax of update statement.
UPDATE table_name SET column1 = newvalue1, column2 = newvalue2,.. WHERE condition
Lets take an example example, updating a column Code value to ‘46354%@’ for Id =1 in table TBLA then update statement would be as shown below.
Update TBLA SET Code='46354%@' WHERE Id =1
You can see you have provided a hard code values ‘46354%@’ for that, but sometimes you may need to update values in table based on data that available in different table.
That may be like fetching values that you want to update in table from another table, or you can say like that updating values from select statement in SQL.
Lets demonstrate, how to update values in table from select statement in SQL.
For this first we will create sample tables and insert data into these tables.
CREATE Table dbo.TBLA
(ID INT Identity(1,1),
Name VARCHAR(100),
Code VARCHAR(10)
)
INSERT INTO dbo.TBLA
(Name)
VALUES
('Rohit Kumar'),
('Ajay Singh'),
('Manoj Pathak'),
('Sanjay Kumar'),
('Atul Negi'),
('Sumit Viyas')
CREATE Table dbo.TBLB
(ID INT Identity(1,1),
Code VARCHAR(10)
)
INSERT INTO dbo.TBLB
(Code)
VALUES
('XCV56%'),
('GSVFB#'),
('6725@5'),
('975$%3'),
('%63514'),
('6587^#')
You can see, now we have two tables named TBLA, and TBLB in database as shown below.
SELECT * FROM dbo.TBLA SELECT * FROM dbo.TBLB
You can see, In table TBLA, values in code columns are blank that will be updated with values that we have in Code column in table TBLB.
Update values using SQL JOIN
Lets update the values in Code columns for TBLA using JOIN, Code values in table TBLA will be updated by Joining TBLA with table TBLB which contains Code values as shown below.
UPDATE A SET A.Code= B.Code FROM dbo.TBLA A INNER JOIN dbo.TBLB B ON A.ID = B.ID
You can see, records are updated. Lets check the table TBLA.
SELECT * FROM dbo.TBLA SELECT * FROM dbo.TBLB
You can see, now values in Code columns in table TBLA are updated, new code values are selected based on JOIN that is applied between TBLA and TBLB, and updated in TBLA.
Update code values using Subquery
You can also use subquery to update code values in table TBLA, Subquery select new code values from table TBLB and that is used by update statement to update values in column.
First update a blank values in code column for TBLA, as we just have updated new values to code column in above T-SQL statement.
UPDATE dbo.TBLA SET Code =''
Lets see the records on table TBLA, you can see now code values is blank for each records in table.
SELECT * FROM dbo.TBLA
Lets update from select using Subquery as shown below.
UPDATE dbo.TBLA SET TBLA.CODE = ( SELECT B.CODE FROM dbo.TBLB B WHERE B.ID = TBLA.ID )
Lets see the records in table, and you can see the values in code column are updated.
SELECT * FROM dbo.TBLA
Also Read..
