How to Update from Select Statement in SQL

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

Update statement.

SQL Basics Tutorial	SQL Advance Tutorial	SSRS	Interview Q & A
SQL Create table	SQL Server Stored Procedure	Create a New SSRS Project	List Of SQL Server basics to Advance Level Interview Q & A
SQL ALTER TABLE	SQL Server Merge	Create a Shared Data Source in SSRS	SQL Server Question & Answer Quiz
SQL Drop	SQL Server Pivot	Create a SSRS Tabular Report / Detail Report
..... More	.... More	....More
Power BI Tutorial	Azure Tutorial	Python Tutorial	SQL Server Tips & Tricks
Download and Install Power BI Desktop	Create an Azure storage account	Learn Python & ML Step by step	Enable Dark theme in SQL Server Management studio
Connect Power BI to SQL Server	Upload files to Azure storage container		SQL Server Template Explorer
Create Report ToolTip Pages in Power BI	Create Azure SQL Database Server		Displaying line numbers in Query Editor Window
....More	....More		....More

 3,948 total views,  1 views today