CODEWITHSUNDEEP
mysqlfield
budamivardi
budamivardi

Reputation: 760

How to Update a Field From The Same Data in MySQL

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

Upvotes: 0

Views: 44

Answers (2)

Strawberry
Strawberry

Reputation: 33935

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;

Upvotes: 2

Ende Neu
Ende Neu

Reputation: 15773

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)

Upvotes: 2

Related Questions