CODEWITHSUNDEEP
mysqlsql
synic
synic

Reputation: 26668

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

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?

Upvotes: 2

Views: 676

Answers (3)

AD7six
AD7six

Reputation: 66169

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)

Upvotes: 1

mucio
mucio

Reputation: 7119

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

Here we go, with demo in sqlfiddle

Upvotes: 2

Explosion Pills
Explosion Pills

Reputation: 191729

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

Upvotes: 4

Related Questions