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 TutorialSQL Advance TutorialSSRSInterview Q & A
SQL Create tableSQL Server Stored ProcedureCreate a New SSRS Project List Of SQL Server basics to Advance Level Interview Q & A
SQL ALTER TABLESQL Server MergeCreate a Shared Data Source in SSRSSQL Server Question & Answer Quiz
SQL DropSQL Server PivotCreate a SSRS Tabular Report / Detail Report
..... More.... More....More
Power BI TutorialAzure TutorialPython TutorialSQL Server Tips & Tricks
Download and Install Power BI DesktopCreate an Azure storage accountLearn Python & ML Step by stepEnable Dark theme in SQL Server Management studio
Connect Power BI to SQL ServerUpload files to Azure storage containerSQL Server Template Explorer
Create Report ToolTip Pages in Power BICreate Azure SQL Database ServerDisplaying line numbers in Query Editor Window
....More....More....More




 339 total views,  1 views today

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.