mysql - 删除一个表中未被另一个表引用的所有行

Question

我有两个表:

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"));

在“device”表中，last_status_object_id 引用了 status.id。

我想删除所有未被“last_status_object_id”引用的状态行。我似乎无法弄清楚如何实现这一目标。谁能帮帮我？

Answer 1

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