How can change only 2 rows in update tables

Question

I have product picture table. My table's have mainpicture column for each product pictures and in the same time only one pricture should be main picture and other not.

I write this MySQL query:

update product_picture a1,product_picture a2 
set a1.mainpicture=0, a2.mainpicture=1 
where (a1.id=16 and productid=2) 
and (a2.id!=16 and productid=2)

If my product have two picture it's not problem but if product have three or more picture when this query execude one picture mainpicture equal to zero and otheres mainpicture change to one.

I want selected row to zero and one random row set to 1 if there is two or more than picture.

I can't use limit for this query.

Answer 1

You could use IF() to update each picture for a particular product id:

UPDATE product_picture
SET mainpicture = IF(id = 16, 1, 0)
WHERE productid = 2

This would set picture with id 16 as the main picture for product 2.

Answer 2

This is how I solved this issue:

update product_picture a1,product_picture a2 
set a1.mainpicture=0,a2.mainpicture=1 
where (a1.id=16 and a1.productid=2) 
and (a2.id = (
    SELECT * FROM (
        SELECT id 
        FROM `product_picture` 
        WHERE `id` != 16 
        AND `productid`=2 
        ORDER BY RAND() LIMIT 1
    ) cl1)
)

_{Posted on behalf of the question asker}