sada sdadad sada sdadad - 5 months ago 19
SQL Question

INSERT INTO and the subquery with COUNT

I'd like to put photo data to the table

articles_photos
and condition it with numbers of photos for selected article.

Both tables exist. Below I have presented my query

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 says:

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


What did I wrong?

Answer

You are close. I believe the following will work:

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;

You should be able to SELECT from the same table upon which you are inserting, but you can't do it in a subquery inside your VALUES list like you had in your question. Instead, here, we just move all the constants down into the SELECT statement.