Updating Tables Using SELECT - (JOINS, Merge, Sub Query) - SQL Beginner Tutorial
Here I show you 4 ways to Update Tables From Another Table in SQL Server. We use Sub Queries, Select Statements, JOINS and what I call the 'popular method' (you see it all over forums)
Gathering The Data
In this part of the video I run through what data we are going to update and which tables we are using. We need to alter the foreign key constraint at first in order to do the lesson so I show you how to do that here. I also query the source and target table to show which table we are going to update
We use the AdventureWorks2019 database which is a free download from Microsoft (or you can watch my tutorial on setting it up).
Select and Update in One Query
My personal favourite method for quick easy updates. This suits smaller updates and rather that use the IN Operator we reference the values that we need to update and is the perfect example of having select and update in one query. Its a nice beginner method if you are learning SQL Server
Update Using Merge
This is slightly tougher, only because a Merge Statement isn't as easy to write as a sub query, but when checking the execution plan it can be far more efficient in this case so for bigger update statements it's well worth considering especially if you intend on running this query on a regular basis, maybe via SQL Agent
Update With a JOIN
Often a popular question, I guess because we use JOINS so often when querying SQL Server that we want to know up to update the table we have just joined! Here I show you a way to turn your JOIN query into an update. A very basic thing which you will find helpful.
The Popular Method
This is always a suggestion on forums and blogs so I had to include it. Personally it isn't my favourite but it's personal preference. Let me know what you think
Content
00:00 Intro
00:49 Gathering the data
02:30 Update Using a Sub Query
04:02 Update Using Merge
05:05 Update With a Join
06:04 The popular method
07:17 Lesson Summary
