Delete rows in a table that don't match rows in another table

Question

I'm aware this question has been asked before, but the solutions given assume an id (single-column) relationship. I've tried adapting, but the given solutions aren't working for my case.

Update: I must have had my head so far up my own butt that I got turned around. Reviewing the documentation and other code, I see my question and intent should have been to "delete rows in a table that DO match rows in another table." That question is much easier to answer, and I have it working.

I have 2 MySQL INNODB tables - clas and clas_import - that have a few columns in common. I'm importing a CSV into clas_import and I want to delete any rows in clas that don't exist in clas_import.

clas_import is defined like this:

+-----------+----------------------+------+-----+---------+----------------+
| Field     | Type                 | Null | Key | Default | Extra          |
+-----------+----------------------+------+-----+---------+----------------+
| id        | int(10) unsigned     | NO   | PRI | NULL    | auto_increment |
| term      | smallint(4) unsigned | NO   |     | NULL    |                |
| subject   | varchar(10)          | NO   |     | 0       |                |
| catalog   | smallint(5) unsigned | NO   |     | 0       |                |
| component | varchar(10)          | NO   |     | 0       |                |
| section   | varchar(5)           | NO   |     | 0       |                |
+-----------+----------------------+------+-----+---------+----------------+

clas has many more columns, but the id,term, etc columns are defined identical to above.

A unique "class" is defined by a combination of term + subject + catalog + component + section. So I want to find all entries in clas that don't match up to an entry in clas_import, and delete it. The added kicker is that not all rows are being compared - only those where the term row matches certain values

Example data:

clas_import

+----+------+---------+---------+-----------+---------+
| id | term | subject | catalog | component | section |
+----+------+---------+---------+-----------+---------+
|  1 | 1730 | ENG     |     102 | LEC       | 1A04    |
| 15 | 1730 | BLA     |     102 | LEC       | 1A04    |
+----+------+---------+---------+-----------+---------+

clas (columns truncated to only show relevant data)

+----+------+---------+---------+-----------+---------+
| id | term | subject | catalog | component | section |
+----+------+---------+---------+-----------+---------+
| 23 | 1730 | ENG     |     102 | LEC       | 1A04    |
| 26 | 1730 | BLA     |     102 | LEC       | 1A04    |
| 30 | 1730 | ENG     |     100 | LEC       | 1A04    |
| 11 | 1700 | ENG     |     102 | LEC       | 1A04    |
+----+------+---------+---------+-----------+---------+

Desired result: That I can run a query and have row #23 & #26 deleted, while leaving row #30 and #11 untouched.

My query:

DELETE
    FROM `clas`
WHERE
    `clas`.`term` IN (1730) AND
    NOT EXISTS(
        SELECT
            1
        FROM
            `clas_import` as `ci`
        WHERE
            `clas`.`term`      = `ci`.`term` AND
            `clas`.`subject`   = `ci`.`subject` AND
            `clas`.`catalog`   = `ci`.`catalog` AND
            `clas`.`component` = `ci`.`component` AND
            `clas`.`section`   = `ci`.`section`
        )

That query doesn't delete anything. When I change DELETE to SELECT *, no rows are returned. Both the tables are populated, and if I do a simple query matching on the term,subject, etc, I get rows returned - so I know the data is good.

Where is this breaking down?

Edit Here's a dbfiddle: https://dbfiddle.uk/?rdbms=mariadb_10.3&fiddle=6cfd81899fbacb5e567ec84d6e5ed7d6

Answer 1

By using the sample data in your question and the table definitions in your fiddle, I got the result that you want by removing NOT from EXISTS, so I guess your logic was wrong:

DELETE
    FROM `clas`
WHERE
    `clas`.`term` IN (1730) AND
    EXISTS(
        SELECT
            1
        FROM
            `clas_import` as `ci`
        WHERE
            `clas`.`term`      = `ci`.`term` AND
            `clas`.`subject`   = `ci`.`subject` AND
            `clas`.`catalog`   = `ci`.`catalog` AND
            `clas`.`component` = `ci`.`component` AND
            `clas`.`section`   = `ci`.`section`
        )

See the demo.
Results:

> id | term | subject | catalog | component | section
> -: | ---: | :------ | ------: | :-------- | :------
> 11 | 1700 | ENG     |     102 | LEC       | 1A04   
> 30 | 1730 | ENG     |     100 | LEC       | 1A04 

Answer 2

Your query works, there's something else going on. My first guess would be to check for whitespace differences.

But I only just realized that after writing the answer, so here's how you'd do it a bit cleaner.

---

You want a left excluding join to match only the rows in clas which do not match rows in clas_import.

delete clas
from clas
left join clas_import ci on
            clas.term      = ci.term AND
            clas.subject   = ci.subject AND
            clas.catalog   = ci.catalog AND
            clas.component = ci.component AND
            clas.section   = ci.section
      -- This bit makes it exclusive.
where ci.id is null and
      -- And ignore other terms.
      clas.term = 1730;

dbfiddle