查找和更改表中的值

Question

I have a table with images in SQL database. It looks something like this.

SELECT * FROM eshop_images WHERE fg_idProduct='$id' AND main='1'
+----------+-----+------+--------------+
| idImages | url | main | fg_idProduct |
+----------+-----+------+--------------+
|  1       | x   | 0    |      1       |
|  2       | x   | 1    |      1       |
|  3       | x   | 0    |      1       |
|  4       | x   | 0    |      2       |
|  5       | x   | 0    |      2       |
|  6       | x   | 0    |      2       |
|  7       | x   | 1    |      2       |
+----------+-----+------+--------------+

Each product can only have one main image, but what if I want to set some other image which is 0 as main one?

I have to go through the whole table where fg_idProduct=$id and SET them to null. I don't know how to do this, can somebody help me?

Thanks.

Answer 1

Try this:

UPDATE eshop_images
SET main = 0
WHERE main = 1
AND fg_idProduct = $id