mysql - 如果我有一个依赖表并且知道第三个表中的值，请从一个表​​中删除行

Question

我知道 table 房间的房间 GUID (cda32057-b658-4660-a492-7d7838f7a692)。如何删除图像表中的关联行(image_id 212)。

图片

+--------------+------------------------------------------+
| image_id(PK) |                image_name                |
+--------------+------------------------------------------+
|      212     | 62f07048-5080-4d05-a15d-dcc37df8b7eb.jpg |
|              |                                          |
+--------------+------------------------------------------+

图像_房间

+--------------+--------------+
| image_id(PK) | room_id(PK)  |
+--------------+--------------+
|      212     |      192     |
|              |              |
+--------------+--------------+

房间

+-------------+-----------+--------------------------------------+
| room_id(PK) | room_name |              room_guid               |
+-------------+-----------+--------------------------------------+
| 192         | test      | cda32057-b658-4660-a492-7d7838f7a692 |
+-------------+-----------+--------------------------------------+

Answer 1

请尝试:

DELETE FROM image 
WHERE image.image_id IN 
            (SELECT image_room.image_id 
             FROM image_room 
                  INNER JOIN room 
                        ON image_room.room_id = room.room_id 
             WHERE room.room_guid = @__your_GUID__)