SQL Tutorial - How to update data using joins in SQL

Опубликовано: 18 Август 2022
на канале: BeardedDev

4,718

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);