sada sdadad sada sdadad - 6 months ago 9
MySQL Question

MySQL regeneratin image order

I have two SQL tables. The first one is

ARTICLES
with columns like
title
,
content
,
category
,
author
and the second one is
ARTICLES_PHOTOS
and contains data about images (columns
title
,
filename
,
photo_order
and
article_id
foreign key to
ARTICLES
table).

When user deletes an image I want to regenerate the order. For example if we have
{a, b, c, d}
and user deletes b record I want to have
{a, c, d}
with
{1, 2, 3}
order.


  1. How can I do it in the simplest way? Is it any build-in SQL query to
    do it?

  2. Should I iterate through
    ARTICLES_PHOTOS
    records?

  3. Should I collect all
    ARTICLES_PHOTOS
    records and then use UPDATE
    query?


Answer

Just decrease photo_order that are greater than deleted one

update ARTICLES_PHOTOS  
    set photo_order  = photo_order-1 
  where article_id = that_article_id and 
        photo_order > deleted_photo_order