php - 如果另一个表ID匹配则更新一个表

好的，我正在尝试更新一个表，这取决于它是否属于用户，因此在一个表中，例如，我有图片，如下所示：

+----------------------------------------------------+
| picture_id   |   picture_user   |    picture_title |
+----------------------------------------------------+

and in another table I have:

+--------------------------------------------------------------+
| delete_pic_id   |  delete_pic_user  |   delete_pic_status    |
+--------------------------------------------------------------+

now I want to update delete_pic_status only if the delete_pic_user matches the picture_user

Here's what I am thinking but need a little help:

$manage_application = $database->prepare(
            "UPDATE deleted_pic
              SET delete_pic_status=1
             WHERE delete_pic_user(the other table)=the id");

我该怎么做

最佳答案

使用Exists

UPDATE deleted_pic
SET    delete_pic_status = 1
WHERE  EXISTS (SELECT 1
               FROM   other_table ot
               WHERE  ot.picture_user = deleted_pic.delete_pic_user)