SQL INSERT, UPDATE, and DELETE with JOIN

In this article, we will discuss the usage of SQL JOIN statements in the context of INSERT, UPDATE, and DELETE operations.

INSERT JOIN Syntax

INSERT INTO table_name (column1, column2, ...)
SELECT table2.column1, table2.column2, ...
FROM table2
JOIN table1
ON table1.common_column = table2.common_column;

Example

Let's say we have two tables - orders and customers. We want to insert customer_id and order_date from the orders table into the customers table. Here's how we can do it using an INNER JOIN:

INSERT INTO customers (customer_id, order_date)
SELECT orders.customer_id, orders.order_date
FROM orders
JOIN customers
ON orders.customer_id = customers.customer_id;

Output

The specified values from the orders table will be inserted into the customers table.

Explanation

In this query, we used a JOIN statement to join the orders table with the customers table based on the common column, customer_id. We then selected the customer_id and order_date columns from the orders table and inserted them into the customers table.

Use

The INSERT JOIN statement can be used when we want to insert data from one table into another table based on a common column.

Important Points

• The joining columns must have the same data type
• The column order of the SELECT statement must match the column order of the INSERT INTO statement.

UPDATE JOIN Syntax

UPDATE table1
JOIN table2
ON table1.common_column = table2.common_column
SET table1.column1 = table2.column1, table1.column2 = table2.column2;

Example

Let's say we have two tables - customers and orders. We want to update the customer_name column in the customers table with the customer_name column in the orders table where the customer_id in both tables match. Here's how we can do it using an INNER JOIN:

UPDATE customers
JOIN orders
ON customers.customer_id = orders.customer_id
SET customers.customer_name = orders.customer_name;

Output

The specified column in the customers table will be updated with the corresponding values from the orders table.

Explanation

In this query, we used a JOIN statement to join the customers table with the orders table based on the common column, customer_id. We then updated the customer_name column in the customers table with the values in the customer_name column in the orders table.

Use

The UPDATE JOIN statement can be used when we want to update data in one table based on the values in another table.

Important Points

• The tables being updated must have a common column in order to perform the join
• The SET statement must contain the columns to be updated and their corresponding values, separated by commas.

DELETE JOIN Syntax

DELETE table1
FROM table1
JOIN table2
ON table1.common_column = table2.common_column
WHERE condition;

Example

Let's say we have two tables - customers and orders. We want to delete rows from the customers table where the customer_id in both tables match. Here's how we can do it using an INNER JOIN:

DELETE customers
FROM customers
JOIN orders
ON customers.customer_id = orders.customer_id
WHERE orders.order_date < '2020-01-01';

Output

The specified rows in the customers table will be deleted based on the condition.

Explanation

In this query, we used a JOIN statement to join the customers table with the orders table based on the common column, customer_id. We then used a WHERE clause to specify the condition for deletion - that the orders were made before a certain date (in this case, '2020-01-01').

Use

The DELETE JOIN statement can be used when we want to delete data from one table based on the values in another table.

Important Points

• The tables being deleted from must have a common column in order to perform the join
• The WHERE clause must contain the condition for deletion.

Summary

SQL JOIN statements are powerful tools that can be used in conjunction with INSERT, UPDATE, and DELETE operations to perform actions across multiple tables. By utilizing JOINs, we can easily manipulate data across tables based on common columns.