How to UPDATE a table by joining multiple tables in SQL?

To UPDATE a table by joining multiple tables in SQL, let’s create the two tables ‘order’ and ‘order_detail.’ We can update the data of a table using conditions of other joined tables. It is possible to join two or more tables in an UPDATE query.

CREATE TABLE orders (

    order_id INT PRIMARY KEY,
    customer_name VARCHAR(100),
    order_date DATETIME,
    total_orders INT
);
INSERT INTO  orders
SELECT 1, 'Jack', '2020-02-03', 4 UNION ALL
SELECT 2, 'Rose', '2020-01-09', 19;

CREATE TABLE order_details (
    order_detail_id INT  PRIMARY KEY,
    order_id VARCHAR(100),
    item VARCHAR(100)
);

INSERT INTO  order_details
SELECT 1, 1, 'laptop' UNION ALL
SELECT 2, 2, 'mouse';

I just remembered 🤔 that before we talk about the UPDATE part of things, let’s focus on joins for a bit.

What if we told you there is a way to JOIN two or more tables in SQL without actually writing any SQL .

Sounds absurd, right?

Not today.

Not with Datameer.

Watch the video below to see how this is done.

Cool right?

After this is done, we can publish our view back into the snowflake DW and perform our update.

Let’s proceed to show the syntax of  UPDATE with JOIN is different in other DBMS.

1. MYSQL:

In MYSQL, we can update the multiple tables in a single UPDATE query. In the below query, both ‘order’ and ‘order_detail’ tables are updated at once.

UPDATE orders o
INNER JOIN order_details od
  ON o.order_id = od.order_id
SET o.total_orders = 7
    ,item= 'pendrive'
WHERE o.order_id = 1
  AND order_detail_id = 1;

2. SQL SERVER:

In SQL Server, we can join two or more tables, but we cannot update the data of multiple tables in a single UPDATE statement. So, we need an individual UPDATE query to update each table. In the below UPDATE statement only the ‘order’ table is updated.

UPDATE o
SET total_orders = 7
FROM orders o
INNER JOIN order_details od
    ON o.order_id = od.order_id
WHERE customer_name = 'Jack';

So that’s how we can do an UPDATE on join in MySQL and SQLServer..

If you’d like to get insights from your data within minutes, feel free to take datameer for a spin and try it out yourself.

Sign up for your free trial here!

Up Next:

Read How to find the duplicate data in a table in SQL?