sada sdadad sada sdadad - 5 months ago 10
MySQL Question

SQL query with two different ids

I need to prevent

photo_order
field from exceeding number of photos being associated with article. I have this, but it doesn't work. Table name has been used two times and it is probably wrong.

UPDATE articles_photos SET photo_order =
IF(photo_order < (SELECT COUNT(id) FROM articles_photos
WHERE article_id = 12), photo_order + 1, 1) WHERE id = 26


How can I fix above query? My database is MySQL.

Answer

I assume this is the error you are getting:

You can't specify target table 'articles_photos' for update in FROM clause

Here's one work around using a cross join with a subquery:

update articles_photos ap
cross join (select count(id) cnt from articles_photos where article_id = 12) temp
set ap.photo_order = if(ap.photo_order<temp.cnt,ap.photo_order+1,1)
where ap.id = 26;
Comments