MySQL UPDATE ONE TABLE WHERE CONDITION FROM ANOTHER TABLE

 Summary: in this tutorial, you will learn how to use the MySQL UPDATE JOIN statement to perform the cross-table update. We will show you step by step how to use INNER JOIN  clause and LEFT JOIN  clause with the UPDATE statement.

MySQL UPDATE JOIN syntax

You often use joins to query rows from a table that have (in the case of INNER JOIN) or may not have (in the case of LEFT JOIN) matching rows in another table. In MySQL, you can use the JOIN clauses in the UPDATE statement to perform the cross-table update.

The syntax of the MySQL UPDATE JOIN  is as follows:

UPDATE T1, T2,
[INNER JOIN | LEFT JOIN] T1 ON T1.C1 = T2. C1
SET T1.C2 = T2.C2, 
    T2.C3 = expr
WHERE condition

Let’s examine the MySQL UPDATE JOIN  syntax in greater detail:

• First, specify the main table ( T1 ) and the table that you want the main table to join to ( T2 ) after the UPDATE clause. Notice that you must specify at least one table after the UPDATE  clause. The data in the table that is not specified after the UPDATE  clause will not be updated.
• Next, specify a kind of join you want to use i.e., either INNER JOIN  or LEFT JOIN  and a join predicate. The JOIN clause must appear right after the UPDATE clause.
• Then, assign new values to the columns in T1 and/or T2 tables that you want to update.
• After that, specify a condition in the WHERE clause to limit rows to rows for updating.

If you follow the UPDATE statement tutorial, you will notice that there is another way to update data cross-table using the following syntax:

UPDATE T1, T2
SET T1.c2 = T2.c2,
      T2.c3 = expr
WHERE T1.c1 = T2.c1 AND condition

This UPDATE  statement works the same as UPDATE JOIN  with an implicit INNER JOIN  clause. It means you can rewrite the above statement as follows:

UPDATE T1,T2
INNER JOIN T2 ON T1.C1 = T2.C1
SET T1.C2 = T2.C2,
      T2.C3 = expr
WHERE condition

Let’s take a look at some examples of using the UPDATE JOIN  statement to having a better understanding.

MySQL UPDATE JOIN examples

We are going to use a new sample database named empdb in for demonstration. This sample database consists of two tables:

• The  employees table stores employee data with employee id, name, performance, and salary.
• The merits table stores employee performance and merit’s percentage.

MySQL UPDATE JOIN example with INNER JOIN clause

Suppose you want to adjust the salary of employees based on their performance.

The merit’s percentages are stored in the merits table, therefore, you have to use the UPDATE INNER JOIN statement to adjust the salary of employees in the employees  table based on the percentage stored in the merits table.

The link between the employees  and merit tables is the performance  field. See the following query:

UPDATE employees
        INNER JOIN
    merits ON employees.performance = merits.performance 
SET 
    salary = salary + salary * percentage;

How the query works.

We specify only the employees table after UPDATE clause because we want to update data in the  employees table only.

For each row in the employees table, the query checks the value in the performance column against the value in the performance column in the merits table. If it finds a match, it gets the percentage in the merits  table and updates the salary column in the employees  table.

Because we omit the WHERE clause in the UPDATE  statement, all the records in the employees  table get updated.

MySQL UPDATE JOIN example with LEFT JOIN

Suppose the company hires two more employees:

INSERT INTO employees(emp_name,performance,salary)
VALUES('Jack William',NULL,43000),
      ('Ricky Bond',NULL,52000);

Because these employees are new hires so their performance data is not available or NULL .

To increase the salary for new hires, you cannot use the UPDATE INNER JOIN  statement because their performance data is not available in the merit  table. This is why the UPDATE LEFT JOIN  comes to the rescue.

The UPDATE LEFT JOIN  statement basically updates a row in a table when it does not have a corresponding row in another table.

For example, you can increase the salary for a new hire by 1.5%  using the following statement:

UPDATE employees
        LEFT JOIN
    merits ON employees.performance = merits.performance 
SET 
    salary = salary + salary * 0.015
WHERE
    merits.percentage IS NULL;

In this tutorial, we have shown you how to use the MySQL UPDATE JOIN  with the INNER JOIN  and LEFT JOIN  clauses to perform the cross-table update.

Find Duplicate Values in a SQL Table

Generally, it’s best practice to put unique constraints on a table to prevent duplicate rows. However, you may find yourself working with a database where duplicate rows have been created through human error, a bug in your application, or uncleaned data from external sources. This tutorial will teach you how to find these duplicate rows.

To follow along, you’ll need read access to your database and a tool to query your database.

Identify Duplicate Criteria

The first step is to define your criteria for a duplicate row. Do you need a combination of two columns to be unique together, or are you simply searching for duplicates in a single column? In this example, we are searching for duplicates across two columns in our Users table: username and email.

Write Query to Verify Duplicates Exist

The first query we’re going to write is a simple query to verify whether duplicates do indeed exist in the table. For our example, my query looks like this:

SELECT username, email, COUNT(*)
FROM users
GROUP BY username, email
HAVING COUNT(*) > 1

HAVING is important here because unlike WHERE, HAVING filters on aggregate functions.

If any rows are returned, that means we have duplicates. In this example, our results look like this:

USERNAME	EMAIL	COUNT
Pete	pete@example.com	2
Jessica	jessica@example.com	2
Miles	miles@example.com	2

List All Rows Containing Duplicates

In the previous step, our query returned a list of duplicates. Now, we want to return the entire record for each duplicate row.

To accomplish this, we’ll need to select the entire table and join that to our duplicate rows. Our query looks like this:

SELECT a.*
FROM users a
JOIN (SELECT username, email, COUNT(*)
FROM users 
GROUP BY username, email
HAVING count(*) > 1 ) b
ON a.username = b.username
AND a.email = b.email
ORDER BY a.email

If you look closely, you’ll see that this query is not so complicated. The initial SELECT simply selects every column in the users table, and then inner joins it with the duplicated data table from our initial query. Because we’re joining the table to itself, it’s necessary to use aliases (here, we’re using a and b) to label the two versions.

Here is what our results look like for this query:

ID	USERNAME	EMAIL
1	Pete	pete@example.com
6	Pete	pete@example.com
12	Jessica	jessica@example.com
13	Jessica	jessica@example.com
2	Miles	miles@example.com
9	Miles	miles@example.com