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

Please see Why should I provide an MCRE for what seems to me to be a very simple SQL query — Strawberry
– Strawberry, Commented Jan 7, 2020 at 0:04
Are the fields' datatypes the same between the two tables? Have you verified string fields in either table aren't concealing whitespace or even non-printing characters? Do the string fields have the same collation? — Uueerdo
– Uueerdo, Commented Jan 7, 2020 at 0:19
@Uueero - the relevant fields are identical. clas_import was made by copying clas. — Quasipickle
– Quasipickle, Commented Jan 7, 2020 at 0:22
BTW, my bet it is whitespaces. May be not spaces but something like \r. If you provide details what (sql? csv? xml?) and how (exact options if it is load data command) you import your data it may give us some clues. — fifonik
– fifonik, Commented Jan 7, 2020 at 0:41

Schwern · Accepted Answer · 2020-01-07 00:20:08Z

1

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

answered Jan 7, 2020 at 0:20

Schwern

167k28 gold badges225 silver badges370 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Quasipickle Over a year ago

Thanks. This format isn't working just the same as the one I posted. But your answer is so clear that I agree with you - I must have something else going on.

Quasipickle Over a year ago

@fifonik Thanks for your attention to my question. As I've just updated - my problem was actually the exact opposite of what I posted. Thanks again for your help, and sorry for wasting your time.

forpas · Accepted Answer · 2020-01-07 17:19:15Z

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

answered Jan 7, 2020 at 17:19

forpas

165k10 gold badges51 silver badges85 bronze badges

1 Comment

Quasipickle Over a year ago

Thanks. The solution ended up not needing a subquery at all - just some plain WHERE conditions.

Collectives™ on Stack Overflow

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

2 Answers 2

2 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related