SQL Update With Join

Syntax of SQL Update with join?.

To start our journey, let's first understand the language of the SQL UPDATE query with JOIN statement. This combination helps us keep data in sync between tables, making sure our information is up-to-date and accurate.

UPDATE target_table
JOIN source_table ON target_table.join_column = source_table.join_column
SET target_table.column_to_update = source_table.new_value;

In essence, this syntax facilitates the simultaneous update of a target table using data from a source table, establishing a connection through a specified join condition.

Real-world Example : Updating Customer Details

To understand the concept fully, let's dive into a practical example involving a customer table. Imagine you have an updated dataset from another source system, and your mission is to incorporate this latest information into your existing customer table.

UPDATE customer_details
JOIN source_table ON customer_table.customer_id = source_table.customer_id
SET customer_table.cus_name = source_table.new_cus_name;

Here, we're updating the customer table by matching records based on the customer ID, ensuring a seamless integration of the freshest data.

Using SQL Update with JOIN on Several Tables

But what if the complexity extends beyond a simple table update? Fear not! SQL enables us to wield its might across multiple tables effortlessly. Let's explore this situation using two tables called EXE_Table1 and EXE_Table2.

Creating and Populating EXE_Table1 and EXE_Table2

Before we proceed, let's create and populate our two tables to set the stage for our SQL Update with JOIN adventure.

-- Creating Table1

CREATE TABLE EXE_Table1 (column1 INT, column2 INT, column3 VARCHAR(100));
INSERT INTO EXE_Table1 (column1, column2, column3) VALUES
(1, 100, 'TAB1_Value1'),
(2, 200, 'TAB1_Value2'),
(3, 300, 'TAB1_Value3'),
(4, 400, 'TAB1_Value4');

-- Creating Table2

CREATE TABLE EXE_Table2 (column1 INT, column2 INT, column3 VARCHAR(100));
INSERT INTO EXE_Table2 (column1, column2, column3) VALUES
(1, 100, 'TAB2_Value1'),
(2, 200, 'TAB2_Value2'),
(3, 300, 'TAB2_Value3'),
(4, 400, 'TAB2_Value4');

Now, armed with our data, let's proceed to the grand update.

Updating Table1 with Data from Table2

UPDATE EXE_Table1
SET t1.column2 = t2.column2,
t1.column3 = t2.column3
FROM EXE_Table1 t1
JOIN EXE_Table2 t2 ON t1.column1 = t2.column1
WHERE t1.column1 IN (1, 2);

Ensuring Clarity: Checking the Updated Content

To ensure our SQL magic worked as intended, let's check the content of our tables.

-- Checking Table1
SELECT * FROM EXE_Table1;

-- Checking Table2
SELECT * FROM EXE_Table2;

Looking at the tables shows that we've succeeded, and the specific rows in EXE_Table1 are now updated and working well together.