Definition:
Although joins are commonly used for retrieving data from multiple tables, they can also be utilized for updating and deleting data across multiple tables. It refers to the process of modifying or removing data from multiple tables in a database simultaneously, by using a join operation to link the tables based on a common column or set of columns.
When updating with join, the data in one table is modified based on the matching data in another table. For example, you can update a customer’s information in one table and their order history in another table simultaneously using a join.
When deleting with join, the data in one or more tables is removed based on the matching data in another table. For instance, you can delete a customer’s information from one table and their associated orders from another table simultaneously using a join.
Tables used in below examples
Here are the two tables that we will be utilizing to carry out update and delete operations with joins.
CREATE TABLE Sales.Targets (
Target_Id INT PRIMARY KEY,
Percentage DECIMAL(4, 2) NOT NULL DEFAULT
);
INSERT INTO Sales.targets(
Target_Id,
Percentage
)
VALUES
(1, 0.2),
(2, 0.3),
(3, 0.5),
(4, 0.6),
(5, 0.8);
CREATE TABLE Sales.Commissions (
Staff_Id INT PRIMARY KEY,
Target_Id INT,
Base_Amount DECIMAL(10, 2) NOT NULL DEFAULT 0,
Commission DECIMAL(10, 2) NOT NULL DEFAULT 0,
FOREIGN KEY(target_id) REFERENCES Sales.Targets(Target_Id),
);
INSERT INTO Sales.commissions (
Staff_Id,
Base_Amount,
Target_Id
);
VALUES
(1, 100000,2),
(2, 120000,1),
(3, 80000, 3),
(4, 900000,4),
(5, 950000,5);
Update using join :.
Syntax:
Update
Table1
Set
Table1.New_col_Name(Value)
FROM
Table1
JOIN
Table2
ON
Table1.Coll_Name = Table2.Coll_Name
Example:
UPDATE
Sales.Commissions
SET
Sales.Commissions.Commission = C.Base_Amount * T.Percentage
FROM
Sales.Commissions C
INNER JOIN
Sales.Targets T
ON
C.Target_Id = T.Target_Id;
Result:
Delete:
Syntax :
DELETE Table1
FROM
Table1
JOIN
Table2
ON
Table1.Coll_Name = Table2.Coll_Name
WHERE
Condition
Example :
DELETE Sales.Commissions
FROM
Sales.commissions
JOIN
Sales.Targets
ON
Sales.Commissions.Target_Id = Sales.Targets.Target_Id
WHERE
Target_Id = 5
Result :
Benefits of using update & delete with joins
Using join statements to update and delete data in a relational database can offer several benefits:
- Efficiency: When you update or delete records from multiple tables at once, using join statements can often be more efficient than executing multiple separate queries. This is because join statements can perform the operation in a single pass through the data, rather than requiring multiple trips to the database.
- Data Integrity: Using join statements to update or delete records from multiple tables at once can help ensure that your data remains consistent and accurate. This is because join statements can enforce referential integrity, which helps prevent orphaned records and other data inconsistencies.
- Simplicity: By enabling you to carry out multiple operations in a single query, join statements can simplify your SQL code, resulting in code that is easier to maintain and comprehend, while also minimizing the probability of errors.
- Flexibility: Join statements can be used to update or delete data across multiple tables, regardless of the specific relationships between those tables. This means that you can use join statements to perform complex operations on your data, even if your database schema is complex.
Summary :
Overall, using join statements to update or delete data can be a powerful tool for managing relational data. It can simplify SQL code, allowing multiple operations to be performed in a single query, resulting in code that is easier to read and maintain, and less prone to errors. Additionally, join statements can be used to update or delete data across multiple tables, irrespective of the relationships between those tables.