Delete all rows in one table that aren't referenced by another table

Question

I have two tables:

CREATE TABLE  "status" (
    "id" integer NOT NULL PRIMARY KEY,
    "created_at" datetime NOT NULL,    
    "updated_at" datetime NOT NULL);


CREATE TABLE "device" (
    "id" integer NOT NULL PRIMARY KEY,
    "created_at" datetime NOT NULL,
    "updated_at" datetime NOT NULL,
    "last_status_object_id" integer REFERENCES "status" ("id"));

In the table "device", last_status_object_id references status.id.

I want to delete all status rows that are not referenced by "last_status_object_id". I can't seem to figure out how to make this happen. Can anyone help me out?

Are you really using mysql - the quotes are invalid (they should be backticks, probably) — AD7six, Oct 23 '13 at 15:27
@AD7six souble quotes are not invalid. They are ANSI standard and depending on the MySQL settings, they work there, too. See **[ANSI_QUOTES](http://dev.mysql.com/doc/refman/5.5/en/server-sql-mode.html#sqlmode_ansi_quotes)** — ypercubeᵀᴹ, Oct 23 '13 at 15:28
@AD7six Sorry, not trying to confuse or mislead anyone. This is the default django output sql. — synic, Oct 23 '13 at 15:36
You live and learn, me in this case (mysql has a setting for ansi quotes, django uses it) =) — AD7six, Oct 23 '13 at 15:42
Are you looking from something like [this](http://stackoverflow.com/questions/3384127/delete-sql-rows-where-ids-do-not-have-a-match-in-another-table) — heretolearn, Oct 23 '13 at 17:00

score 4 · Accepted Answer · answered Oct 23 '13 at 15:17

4

DELETE
    status
FROM
    status
    LEFT JOIN device ON (status.id = last_status_object_id)
WHERE
    device.id IS NULL

answered Oct 23 '13 at 15:17

Explosion Pills

188,624
52
326
405

I think this is not what I want - if the status row id is not contained anywhere in the device.last_status_object_id, I want to delete it. – synic Oct 23 '13 at 15:21
1

@synic that is what `device.id IS NULL` does – Explosion Pills Oct 23 '13 at 15:23

mucio · Answer 2 · 2013-10-23T15:36:56.550

2

delete from status
where id not in (select last_status_object_id
                            from device);

Here we go, with demo in sqlfiddle

edited Oct 23 '13 at 15:36

answered Oct 23 '13 at 15:18

mucio

7,014
1
21
33

I don't want to delete any device rows. I want to only delete a status row if it's ID is not referenced in device.last_status_object_id – synic Oct 23 '13 at 15:22
sorry, copy and past mistake – mucio Oct 23 '13 at 15:24
Ok, pretend I didn't add the device_id on status. I'm going to remove it, because it's confusing the question. – synic Oct 23 '13 at 15:26

score 1 · Answer 3 · answered Oct 23 '13 at 15:39

1

I want to delete all status rows that are not referenced by "last_status_object_id".

An easy way to express that is using NOT EXISTS:

DELETE FROM 
    status
WHERE
    NOT EXISTS (SELECT * FROM device WHERE last_status_object_id = status.id)

answered Oct 23 '13 at 15:39

AD7six

63,116
12
91
123

Delete all rows in one table that aren't referenced by another table

3 Answers3