mysql - INSERT INTO和带有COUNT的子查询

我想把照片数据放在articles_photos表中，并用所选文章的照片数量对其进行调整。
两个表都存在。以下是我提出的问题

INSERT INTO articles_photos(title,
                            filename,
                            photo_order,
                            created,
                            article_id)
      VALUES ('title',
              'filename',
               (SELECT COUNT(id)
                  FROM articles_photos
                 WHERE article_id = 7) + 1,
              NOW(),
              7)

phpmyadmin说：

Static analysis:

5 errors were found during analysis.

    A comma or a closing bracket was expected (near "SELECT" at position 109)
    Unrecognized keyword. (near "COUNT" at position 116)
    Unexpected token. (near "(" at position 121)
    Unexpected token. (near "id" at position 122)
    Unexpected token. (near ")" at position 124)

#1093 - Table 'articles_photos' is specified twice, both as a target for 'INSERT' and as a separate source for data

我做错什么了？

最佳答案

你很接近。我相信以下措施会奏效：

INSERT INTO articles_photos(title,
                        filename,
                        photo_order,
                        created,
                        article_id)
SELECT 'title',
    'filename',
    COUNT(id)+1,
    now(),
    7
FROM articles_photos
WHERE article_id = 7;

您应该能够从要插入的同一个表中进行选择，但不能像在问题中那样在VALUES列表中的子查询中进行选择。相反，在这里，我们只是将所有常量下移到SELECT语句中。