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