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.
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.
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:
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.
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.