How to UPDATE a table by joining multiple tables in SQL?
- How-Tos FAQs
- December 16, 2018
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 with Datameer.
Watch the video below to see how this is done.
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.
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.