SQL Tutorial - How to update data using joins in SQL

Learn how to update data using joins in SQL and much more, in this video we will cover how to perform updates using joins in SQL, how to update multiple columns at the same time, how to update data using CTEs and how to update data within a transaction, we will also cover what sanity checks need to carried when out when performing updates.
#beardeddev #sql #data #dataengineering #dataanalysis
To follow along with the video you can use the code below:
IF OBJECT_ID(N'dbo.Product', N'U') IS NOT NULL
DROP TABLE dbo.Product;
CREATE TABLE dbo.Product
(
ProductKey INT IDENTITY(1, 1) NOT NULL
CONSTRAINT PK_Product_ProductKey PRIMARY KEY (ProductKey),
Code CHAR(8) NOT NULL
CONSTRAINT UN_Product_Code UNIQUE (Code),
[Name] VARCHAR(50) NOT NULL,
Category VARCHAR(50) NOT NULL,
Price DECIMAL(6, 2) NOT NULL,
CreatedDate DATETIME2 NOT NULL
CONSTRAINT DF_Product_CreatedDate DEFAULT (CURRENT_TIMESTAMP),
ModifiedDate DATETIME2 NOT NULL
CONSTRAINT DF_Product_ModifiedDate DEFAULT (CURRENT_TIMESTAMP)
);
INSERT INTO dbo.Product (Code, [Name], Category, Price)
VALUES
('CMP76460', 'Laptop', 'Computing', 399.99),
('CMP73900', 'Monitor', 'Computing', 189.99),
('CMP68194', 'PC', 'Computing', 749.99),
('APP16830', 'Washing Machine', 'Appliances', 519.99),
('APP14936', 'Refridgerator', 'Appliances', 329.99),
('APP19364', 'Cooker', 'Appliances', 689.99),
('TVA50385', 'Television', 'TV & Audio', 179.99),
('TVA50275', 'Projector', 'TV & Audio', 249.99);
IF OBJECT_ID(N'dbo.ProductStaging', N'U') IS NOT NULL
DROP TABLE dbo.ProductStaging;
CREATE TABLE dbo.ProductStaging
(
Code CHAR(8) NOT NULL,
[Name] VARCHAR(50) NOT NULL,
Category VARCHAR(50) NOT NULL,
Price DECIMAL(6, 2) NOT NULL
);
INSERT INTO dbo.ProductStaging (Code, [Name], Category, Price)
VALUES
('CMP76460', 'Laptop', 'Computing', 479.99),
('CMP73900', 'Monitor', 'Computing', 209.99),
('CMP68194', 'PC', 'Computing', 749.99),
('APP16830', 'Washing Machine', 'Appliances', 519.99),
('APP14936', 'Refridgerator', 'Appliances', 349.99),
('APP19364', 'Cooker', 'Appliances', 689.99),
('TVA50385', 'Television', 'TV & Audio', 199.99),
('TVA50275', 'Projector', 'TV & Audio', 249.99);

[@houstonvanhoy7767]

Brad: At 6:27, I thought of posing the question - isn't this a good time to use BEGIN TRAN, etc? At 20:45, you answered my question. Great minds think alike.

[@NIN1]

Love all your tutorials mate, thanks for all the help!

[@BeardedDevData]

Thanks for the support, means a lot.

[@MrXankiller]

You really got a thing for teaching, makes it easy to understand

[@BeardedDevData]

Thanks so much, that's exactly why I set up the channel.

[@hasanmougharbel8030]

Hey my friend, blessed to discover your channel.
I have only a simple enquiry as a newbie sql learner.
What is the main difference between alter and update in terms of their application in sql?
Thanks for taking time on this..

[@BeardedDevData]

Hi Hasan, ALTER is Data Definition Language, it is used to change objects e.g. ALTER TABLE, ALTER VIEW, if I want to add a column to an existing table I will need to alter the definition of the table. UPDATE is Data Manipulation Language, this is used to change data, e.g. if I want to change the name of a product in a table I will use UPDATE.

[@hasanmougharbel8030]

@@BeardedDevData great man...when i alter a table, i may change its title , or adding or deleting column names, or adding constraints, is that correct of what alter can do?

[@BeardedDevData]

Yes, you just can't change the object name so easily, have a look at this video: ua-cam.com/video/KDoaM76UA0g/v-deo.html

[@sndrstpnv8419]

can you share code pls

[@BeardedDevData]

It's in the description.

[@sndrstpnv8419]

@@BeardedDevData thanks, do you have video for windows functions with code pls

[@BeardedDevData]

Sure, I have done a follow along video: ua-cam.com/video/lBcDSsgp0RU/v-deo.html