TheDeveloperBlog.com

Home | Contact Us

C-Sharp | Java | Python | Swift | GO | WPF | Ruby | Scala | F# | JavaScript | SQL | PHP | Angular | HTML

SQL UPDATE with JOIN

SQL update with join query with sql, tutorial, examples, insert, update, delete, select, join, database, table, join

<< Back to SQL

SQL UPDATE with JOIN

SQL UPDATE JOIN means we will update one table using another table and join condition.

Let us take an example of a customer table. I have updated customer table that contains latest customer details from another source system. I want to update the customer table with latest data. In such case, I will perform join between target table and source table using join on customer ID.

Let's see the syntax of SQL UPDATE query with JOIN statement.

UPDATE customer_table
INNER JOIN
Customer_table
ON customer_table.rel_cust_name = customer_table.cust_id
SET customer_table.rel_cust_name = customer_table.cust_name

How to use multiple tables in SQL UPDATE statement with JOIN

Let's take two tables, table 1 and table 2.

Create table1

CREATE TABLE table1 (column1 INT, column2 INT, column3 VARCHAR (100))
INSERT INTO table1 (col1, col2, col3)
SELECT 1, 11, 'FIRST'
UNION ALL
SELECT 11,12, 'SECOND'
UNION ALL 
SELECT 21, 13, 'THIRD'
UNION ALL 
SELECT 31, 14, 'FOURTH'

Create table2

CREATE TABLE table2 (column1 INT, column2 INT, column3 VARCHAR (100))
INSERT INTO table2 (col1, col2, col3)
SELECT 1, 21, 'TWO-ONE'
UNION ALL
SELECT 11, 22, 'TWO-TWO'
UNION ALL 
SELECT 21, 23, 'TWO-THREE'
UNION ALL 
SELECT 31, 24, 'TWO-FOUR'

Now check the content in the table.

SELECT * FROM table_1
SELECT * FROM table_2
Col 1Col 2Col 3
1111First
21112Second
32113Third
43114Fourth

Col 1Col 2Col 3
1121Two-One
21122Two-Two
32123Two-Three
43124Two-Four

Our requirement is that we have table 2 which has two rows where Col 1 is 21 and 31. We want to update the value from table 2 to table 1 for the rows where Col 1 is 21 and 31.

We want to also update the values of Col 2 and Col 3 only.

The most easiest and common way is to use join clause in the update statement and use multiple tables in the update statement.

UPDATE table 1
SET Col 2 = t2.Col2,
Col 3 = t2.Col3
FROM table1 t1
INNER JOIN table 2 t2 ON t1.Col1 = t2.col1
WHERE t1.Col1 IN (21,31)

Check the content of the table

SELECT FROM table 1

SELECT FROM table 2

Col 1Col 2Col 3
1111First
21112Second
32123Two-Three
43124Two-Four

Col 1Col 2Col 3
1121First
21122Second
32123Two-Three
43124Two-Four

Here we can see that using join clause in update statement. We have merged two tables by the use of join clause.

Next TopicSQL UPDATE DATE




Related Links:


Related Links

Adjectives Ado Ai Android Angular Antonyms Apache Articles Asp Autocad Automata Aws Azure Basic Binary Bitcoin Blockchain C Cassandra Change Coa Computer Control Cpp Create Creating C-Sharp Cyber Daa Data Dbms Deletion Devops Difference Discrete Es6 Ethical Examples Features Firebase Flutter Fs Git Go Hbase History Hive Hiveql How Html Idioms Insertion Installing Ios Java Joomla Js Kafka Kali Laravel Logical Machine Matlab Matrix Mongodb Mysql One Opencv Oracle Ordering Os Pandas Php Pig Pl Postgresql Powershell Prepositions Program Python React Ruby Scala Selecting Selenium Sentence Seo Sharepoint Software Spellings Spotting Spring Sql Sqlite Sqoop Svn Swift Synonyms Talend Testng Types Uml Unity Vbnet Verbal Webdriver What Wpf