How to Update a Field From The Same Data in MySQL

Question

id     product    main_image
---    ---------- -----------
1      1          0
2      1          0
3      1          0

4      2          0
5      2          0
6      2          0

7      3          0
8      3          0
9      3          0
10     3          0

I want to use mysql query to make table field datas (for main_image) like this

id     product    main_image
---    ---------- -----------
1      1          1
2      1          0
3      1          0

4      2          1
5      2          0
6      2          0

7      3          1
8      3          0
9      3          0
10     3          0

how can I do it? I want to set a main image for products. I have about 3 millions record, I tried php but its too slow

Given that the main image is always the first one wonders why you'd need to do this! — Strawberry, Sep 02 '14 at 09:32
I used Group by to get first image but it is not usefull for me and makes processor tired so I set a main image and use where. And I need a main image for each of 200.000 hotels to show customers at the first view — budamivardi, Sep 02 '14 at 09:48

Ende Neu · Accepted Answer · 2014-09-02T09:26:08.803

2

You can use a subquery to select the smaller id form the table:

UPDATE myTable
SET main_image = 1
WHERE id IN
  (SELECT MIN(temp.id)
   FROM (SELECT * FROM myTable) AS temp
   GROUP BY temp.product)

edited Sep 02 '14 at 09:26

answered Sep 02 '14 at 09:16

Ende Neu

15,581
5
57
68

Navicat give error "You can't specify target table 'yk_otel_hotel_pictures' for update in FROM clause" my query UPDATE yk_otel_hotel_pictures SET main = '1' WHERE imageURL IN (SELECT imageURL FROM yk_otel_hotel_pictures GROUP BY hotelID) – budamivardi Sep 02 '14 at 09:20
Mysql doesn't allow select in subquery on the same table, an easy solution can be found [here](http://stackoverflow.com/questions/4429319/you-cant-specify-target-table-for-update-in-from-clause), I made an edit. – Ende Neu Sep 02 '14 at 09:26
I guess there's a more elegant way of writing this. – Strawberry Sep 02 '14 at 09:30
I dublicated table and query worked, thanks every body, thanks Ende – budamivardi Sep 02 '14 at 09:40

score 2 · Answer 2 · answered Sep 02 '14 at 09:40

This...

UPDATE my_table x 
  JOIN 
     ( SELECT product
            , MIN(id) min_id 
         FROM my_table 
        GROUP 
           BY product
     ) y 
    ON y.product = x.product 
   AND y.min_id = x.id 
   SET x.main_image = 1;

... or just this ...

UPDATE my_table x 
  JOIN 
     ( SELECT MIN(id) min_id 
         FROM my_table 
        GROUP 
           BY product
     ) y 
    ON y.min_id = x.id 
   SET x.main_image = 1;

How to Update a Field From The Same Data in MySQL

2 Answers2