In this article, we will learn, How can I do an UPDATE statement with JOIN in SQL Server? I’m working on project a in which I came across a situation where I have to write SQL update query using joins. Now we will see some of the easiest techniques here.
Generic Syntext Query:
UPDATE tb1
SET foo = tb2.col
FROM Table1 tb1
JOIN Table2 tb2
ON tb1.col1 = tb2.colx
WHERE .your condition for updating value.
Sql Query Example :
As you can see in the below image , I have created two table Company_Employees and Department.Company_Employees has a foreign key relationship with the department table.
Now I want to update the “Designation” column with the ‘DepartmentName‘ filed in Department.For that we can use the below query.
UPDATE tb1
SET Designation = tb2.DepartmentName
FROM Company_Employees tb1
JOIN Department tb2
ON tb1.DepId = tb2.Id
WHERE tb1.Id=9
Cretae Script of Both Table for Practice
CREATE TABLE [dbo].[Company_Employees](
[Id] [int] IDENTITY(1,1) NOT NULL,
[First_Name] [nvarchar](200) NULL,
[Middle_Name] [nvarchar](200) NULL,
[Last_Name] [nvarchar](200) NULL,
[Address] [nvarchar](500) NULL,
[Designation] [nvarchar](200) NULL,
[DepId] [int] NULL,
CONSTRAINT [PK_TblUsers] PRIMARY KEY CLUSTERED
(
[Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF,
ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[Department](
[Id] [int] IDENTITY(1,1) NOT NULL,
[DepartmentName] [nvarchar](50) NULL,
CONSTRAINT [PK_Departments] PRIMARY KEY CLUSTERED
(
[Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF,
ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
Insert Query :
INSERT [dbo].[Company_Employees] ([Id], [First_Name], [Middle_Name], [Last_Name], [Address], [Designation], [DepId]) VALUES (9, 'John', 'eric', 'smith', 'Paris', 'Data Analyst', 1)
GO
INSERT [dbo].[Company_Employees] ([Id], [First_Name], [Middle_Name], [Last_Name], [Address], [Designation], [DepId]) VALUES (1010, 'Ram', 'Kumar', 'Verma', 'New Delhi', NULL, 2)
GO
GO
INSERT [dbo].[Department] ([Id], [DepartmentName]) VALUES (1, 'Data Analyst')
GO
INSERT [dbo].[Department] ([Id], [DepartmentName]) VALUES (2, ' Associate Data Engineer
')
GO
INSERT [dbo].[Department] ([Id], [DepartmentName]) VALUES (3, 'Business Intelligence Manager')
GO
INSERT [dbo].[Department] ([Id], [DepartmentName]) VALUES (4, 'Data Engineering')
Generic Query:
Update t1.Column1 = value
from table_name1 as t1
inner join table_name2 as t2 on t2.ID = t1.SomeId
where t1.[column1]=value and t2.[Column1] = value;
The post [Solved]-How To Update a Table using JOIN in SQL Server? appeared first on Software Development | Programming Tutorials.
Read More Articles
- Linq to SQL Group by and Sum in Select | Linq To SQL Group By and Sum
- How send an HTTP POST request to a server from Excel using VBA?
- What is Difference between where and having clauses in SQL server
- How to Use EXCEPT Operator with an example in Sql
- How to use merge statement in the SQL server
- How to write Re-runnable SQL server query
- How to create Cursor in sql server with example
- How to generate random data in sql server for performance testing
- How to write subquery in select statement in Sql
- How to Log record changes in SQL server in an audit table
- Load SQL query result data into cache in advance
- Transpose Row to Column using SQL Server
- SQL Server full text search - large query
- SQL pivot columns null (code/results included)
- Set-based alternative to loop in SQL Server
- What is the best way to store Binary data (images, pdf's, mp3's, videos ect) in mysql and why?
- Database Dependent Caching w/ Multiple Databases/SqlServers
- CASE statement versus temporary table
- TSQL - Filter group if it contains all null
- SQL "transform" query
- ExecuteNonQuery on a stored proc causes it to be deleted
- Does '&' mean something special in sql?
- Is there a maximum size for the array after the T-SQL IN operator?
- How to retrieve existing indexes on tables in SQL Server using code?
- doing select from stored procedure such as : select * from sp_tables
- How to Order BY without having Column in SELECT
- SQLCLR Assembly deployment failed because assembly failed verification
- How can I change password for sql login to database using pyodbc?
- What connection string and or OLEDB/ODBC drivers when moving "classic ASP/ADO" app from SQL Server 2008 to SQL Server 2017?
- SQL FOR XML Path, returning multiple child elements
- SHA256 base 64 hash generation in SQL Server
- Types don't match between the anchor and the recursive part in column of recursive query
- How do I get the date of the last day of the previous month in the previous year in SQL?
- How to make my SSAS cube production ready
- Why would a Stored Procedure run slower than naked T-SQL?
- Sql Server - Precision handling
- Data type conversion in SQL query
- SQL Database in restoring mode
- SQL - char MDDYY or MMDDYY `to datetime
- Diff / Delta script: ideas on streamlining it?
- Run-time error '3708': Parameter Object is improperly defined. Inconsistent or incomplete information was provided
- SQL Server Exception:Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding
- Get the length of a `FOR JSON AUTO` result in SQL Server?
- Display the designation based on their achievement in SQL Server
- Replacing XML elements in SQL server with descending value
- Why does pivoting of a string data column work with row_number?
- Good Strategy for Message Queuing?
- what exactly does it mean to not do work in the database if we can do it elsewhere?
- Must declare the scalar variable, Why when its a Param?
- Getting a bit value from stored procedure in C#
- Is this a SSIS use case? (new to SSIS)
- sql split after certain lenght of charcters to new row
- SQL Server where column in where clause is null
- SQL count query
- Fastest way to convert NVARCHAR value to XML
- How can set Bit type input parameter optional set false by default
- How can I take flat rows of relational data and put these into a C# class?
- Query to replace ID with Name within the same table?
- Modify data entry in a column with IDENTITY property
- Update String Columns without Using Single Quotations - General Question
- Compare values with in a table in SQl
- Truncating Large Tables
- Select last row of each unique combination in SQL Server
- Problems returning name in table 2 from id in table 1
- How to capitalize the first letter of each word in a string in SQL Server
- ScriptingOptions.BatchSize has no effect
- T-SQL Deletes all rows from a table when subquery is malformed
- Enforcing effective dated records in sql
- Filter on Date not working
- Combining unrelated queries into one query to produce counts
- Adding auto increment extra column to view which is not present in table in SQL Server
- ERROR_PROCEDURE() returning NULL for code executed by SP_EXECUTESQL
- MSSQL_CONNECT returns nothing - no error but no response either
- SQL Server 2008 List rows with a duplicate value
- Sqoop import TCP/IP Connection Refused
- Is there some way for me to generate SQL Scripts from an already existing database?
- SQL Server insert from flat file
- I want to connect to an external DB
- SQL Server sys.databases vs sysdatabases?
- sql query not printing
- How to customize migration generation in EF Core Code First?
- Delete everything after the first white space in sql
- Query to distribute value from one table to another by 1
- Cannot insert duplicate key SQL
- Reference a foreign key and Add check constraint for column in parent table
- Error using CSV string with IN Operator
- Make data in database read-only but allow schema changes
- Using Case Statement in linked servers
- Cast to INT Fails
- How to add years to date in SQL?
- Filter Duplicates in UNION with SQL Server
- EF 6 Configured for one-to-one relationship sometimes violates the constraint
- Entity Framework Core 2 auto generated columns
- Can I return a column with it's value as two columns?
- Invalid column name 'False' (Classic asp, table edit issue)
- Returning a single sum from 2 SQL queries
- SQL Trigger Updating a different table when the data changes in another table
- TimeZone shifts using TSQL
- Gracefully terminate long running SQL Server query from ADO.NET
- Adjusting a column value by subtracting previous running total from current row value in SQL Server
- System.IndexOutOfRangeException when trying to return 'select count (Id)'
- INSERT single quoted value for all column types in SQL Server
- If Else statement on T-SQL calculated column
- Drop large set of tables in SQL Server
- Retrieve last element of a column from each group and use it as first element of the same column in next group
- Why Datatables return Null when it is executed via SQL Command text?
- SSAS - Many to many dimension with "one" table
- Get records from two tables when one table has same values in a field in his foreign key
- How to skip an error in a stored procedure
- SQL Server : get the row Identity
- Counting the number of returning item for a timeframe
- Fetch multiple resultset from SQL Server in Laravel 5.3
- CAST value with two decimals - 0.00.0
- SQLSTATE 01000 in SQL Server job output
- ON COLUMNS compared to ON ROWS
- How to drop Or Disable Sql Server Login Trigger?
- Update existing database with Entity Framework Code First in MVC
- Performance of ON Clause over WHERE Clause
- SSIS import CSV file to existing table, using data type and sizes
- How GRANT EXECUTE and cross-database reference works in SQL Server?
- sql server field coming through as blank even though len([field]) shows characters
- How To Fill All Null Values In Table with Stored Procedure
- The multi-part identifier could not be bound sql for query spanning multiple tables
- Node.js connection to SQL Server using instance name, port, and domain
- Create a User defined function like SQL server 2017 STRING_AGG on earlier versions
- Passing foreign language characters to/from a database
- SQL UNION query not working
- syntax error in sql server 2008 for AUTO_INCREMENT
- C# SqlCommand initialization string
- How to replace STRING_AGG and CONCAT in MS SQL Server 2008?
- SQL Server 2008 - Get Latest Record from Joined Table
- Entity Framework Code First with SQL Server Synonyms
- sql: BETWEEN v1 AND v2
- Cannot connect to my database from within SQL Management Studio
- If a thread interupts a spring transaction template mid execution is it guaranteed to roll back?
- Allow updating column over trigger but prevent direct update of the same column
- Boolean equivalence
- Creating a asp webform to query a database with an input
- GRANT EXECUTE to all stored procedures
- SQL server 2005 group by with same column but different date and time?
- SQL - Select doesn't retrieve results
- Procedure returning 0 instead of higher number
- A 4-byte Unsigned Int for Sql Server 2008?
- SQL concat select count and varchar
- Use of SQL Server Management Objects in an application
- Full Text Not Returning The Expected Results
- Use Always Encrypted on decimal(18,2)
- SQL - Incorrect syntax near the keyword 'AND'
- Find duplicate groups of rows in SQL Server
- In MSSQL filter rows based on an ID exists in a column as comma separated string
- t-sql : get a single row with parents, children, grandchildren... n children
- Passing SSRS Dataset Parameters on the fly
- SQL "WHERE" with Division
- Best way to track changes and make changes from Mysql -> MSSQL
- SQL Server Stored Procedure capture return value in T-SQL
- How can I ensure one-at-a-time processing in SQL Server?
- Appropriate .Net data type for decimal(20,0)
- Get all years between 2 provided dates
- TSQL: How to convert local time to UTC? (SQL Server 2008)
- SQL Server XSD SimpleType xs:list as table?