Learn more about the SQL UPDATE JOIN Statement

For every application that fetches and utilizes data, data storage is very important. In the digital world, we use databases to store information units. To communicate with these databases, we use a language known as Structured Query Language (SQL). This is also the standard language for relational database management systems (RDMS).  In SQL, you use statements such as Update, Select, Insert, Create, or Drop to manipulate data stored in the database. Examples of RDMSs that use SQL are Oracle, MS SQL Server, Access, Ingres, Sybase, and so on.

Learn more about SQL. Take a course at Udemy.com.

To create an entry in the database, we would use the Create statement. If you want to make any changes to this created entry, you would have to use the Update statement. Here is the syntax for a regular Update statement:

UPDATE table_name
 SET columno1=valueno1,columnno2=valueno2,...
 WHERE columno=value;

In the above code, for a specific table_name, we set the value of columno1 to valueno1 and columno2 to valueno2, with a condition. The condition is set by the Where statement, which says make the update wherever the condition is found true.

We use the Join statement in SQL to combine rows from two or more tables, provided there exist a common column or field between them. The most commonly use Join is the SQL INNER JOIN statement. This statement will combine the two rows if the condition specified is found true. Here is a sample code for the Join statement:

SELECT column_name
FROM table1
INNER JOIN table2
ON table1.column_name=table2.column_name;

SQL UPDATE JOIN

The Update statement and the Join statement work individually as well as in conjunction. When we need to check if records from a particular table exists in another table and at the same time, update one of the tables based on the result of the earlier check, we can use the SQL UPDATE JOIN statement.

Though the most commonly used Join is the inner join or the simple join, in this article, we will focus on the following types of SQL JOINs, which are as follows:

• INNER JOIN
• LEFT JOIN

Before we go ahead with understanding how the joins function with the Update statement, let us create the following table to perform the individual statements on:

Here is the sample code for  creating a table to help you understand the SQL UPDATE JOIN statements.

Are you new to database programming. Take a course at Udemy.com.

CREATE DATABASE IF NOT EXISTS employeedb;
 
-- create tables
CREATE TABLE merit (
perf INT(12) NOT NULL,
percent FLOAT NOT NULL,
PRIMARY KEY (perf)
);
 
CREATE TABLE employees (
emp_id INT(12) NOT NULL AUTO_INCREMENT,
emp_name VARCHAR(255) NOT NULL,
perf INT(12) DEFAULT NULL,
salary FLOAT DEFAULT NULL,
PRIMARY KEY (emp_id),
CONSTRAINT fk_perf
FOREIGN KEY(perf)
REFERENCES merits(perf)
);
-- insert data for merit table
INSERT INTO merit(perf,percent)
VALUES(1,0),
(2,0.01),
(3,0.03),
(4,0.05),
(5,0.08);
-- insert data for employees table
INSERT INTO employees(emp_name,perf,salary)
VALUES(Mike Doe', 1, 55000),
(Mary Greenspan, 3, 60000),
(Grace Smith, 4, 77000),
(Sue Johnson, 5, 110000),
(John Dell, 3, 76000),
(Nancy Jackson, 2, 40000),
(Jasmine Bush', 3, 56000);

SQL UPDATE with INNER join

The merit percentages are stored in the merit table and the employee information is stored in the employees table. Now, if you want to make adjustments to the salary based on employee performance, you will use the UPDATE INNER JOIN statement to adjust employee’s salary from the employees table based on percent in the merit table. The common field between these two tables is perf, which is performance. Here is the sample query you will use to complete this task:

UPDATE employees
INNER JOIN merit ON employees.perf = merits.perf
SET salary = salary + salary * percent
Output:

emp_name

perf

salary

Mike Doe

1

55000

Mary Greenspan

3

61800

Grace Smith

4

80080

Sue Johnson

5

115500

John Dell

3

78280

Nancy Jackson

2

40800

Jasmine Bush

3

57680

SQL UPDATE LEFT JOIN

In case, we add two new employees to the employees table, we do not have their performance data in the merit table since they are new hires. In such a case, if we use the SQL UPDATE INNER JOIN statement, the result will be wrong. Hence, in such situations we use the SQL UPDATE LEFT JOIN statement. In situations, where corresponding record does not exist in the other table, this type of UPDATE JOIN query is used. Here is the sample code:

INSERT INTO employees(emp_name,perf,salary)
VALUES(Rudy John,NULL,43000),
(Max Rockwell,NULL,52000);
UPDATE employees
LEFT JOIN merit ON employees.perf = merits.perf
SET salary = salary + salary * 0.015;
WHERE merit.percent IS NULL

Now, in this case, since we don’t have the corresponding percentage for these new entries, we are providing the revision of 1.5% in the query itself. The Where statement tells exactly where this change should take place or the entries that it should affect.

Output:

You can also use the RIGHT JOIN statements and the FULL JOIN statements with the update statements. Use the RIGHT JOIN statement when you need to returns all rows from right table with matching rows from the left table and update the resultant entries. If there is no match found, the result is NULL on the left table. When we mention left and right, it means in the syntax where we specify the actual condition for the JOIN. The FULL JOIN statement combines the result of both the LEFT JOIN and RIGHT JOIN, thus even if no match to the condition is found, the query will return all the rows and update based on the update condition.

Though this statement is very useful and simplifies many complex database commands, if more than one match is found for the join in the target table, the results can be random. Hence, ensure that the JOIN is performed only on unique entries.

Get started with mastering SQL programming with course on Udemy.com.