Update particular value in table column mysql

Question

I have table like this:

id business_id path     photo
 1          11 1.jpg        0
 2          11 2.jpg        0
 3          15 1.jpg        0
 4          15 3.jpg        0
 5          15 4.jpg        0
 6          21 scan1.jpg    0
 7          21 scan2.jpg    0
 8          22 1.jpg        1

....

I want to update table so that for each business lets say in above scenario for business 11. one of the path should have photo set to 1.

If photo is already set to 1 (like for business_id = 22) above it should not change that.

I think below query will work for 1 business_id at a time:

Update business 
set photo = 1 
where id  = 11 limit 1

But my question is how can I run this in a batch? So I can update all at once.

M Khalid Junaid · Accepted Answer · 2014-10-24 11:53:18Z

1

You can update your table by using below query,It will get the maximum for each business_id which has all photos set to 0 and using join you sub select will update a single record per business_id to photo = 1

update business b
join (select max(id) id, business_id
from business 
group by business_id
having sum(photo = 0) = count(*)) t
on(b.id = t.id)
set b.photo = 1

DEMO

answered Oct 24, 2014 at 11:53

M Khalid Junaid

64.6k10 gold badges93 silver badges120 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

Joe Murray Over a year ago

This would be correct if count() were changed to 0, since as I understand question he wants 1 photo=1 for each distinct id.

NoviceMe Over a year ago

@JoeMurray - Can you please tell which part of query to change?

M Khalid Junaid Over a year ago

@JoeMurray see the demo part of answer ,also sum(photo = 0) = count(*) will will ensure the resultant business_ids have all photos set to 0 and updating part will update only 1 row per business_id with photo = 1

NoviceMe Over a year ago

@MKhalidJunaid - If i am correct if i set min instead of max it will mark first photo rather than last right?

M Khalid Junaid Over a year ago

@NoviceMe yes you are correct you can set the minimum one by using min

JCalcines · Accepted Answer · 2014-10-24 12:17:23Z

1

You could try this query:

UPDATE business SET photo = 1 WHERE id IN
   (SELECT MIN(id) , MAX(photo) FROM business
    GROUP BY business_id HAVING max(photo) = 0)

Additional info: The next query gives you a list of rows where business doesn't have any photo. This query was the initial point for my answer.

SELECT business_id , MAX(photo) FROM business
GROUP BY business_id HAVING max(photo) = 0

edited Oct 24, 2014 at 12:17

answered Oct 24, 2014 at 11:50

JCalcines

1,28612 silver badges27 bronze badges

2 Comments

Joe Murray Over a year ago

This would result in setting a second photo to 1 for an id that already had a 1.

JCalcines Over a year ago

It didn't should. I am skipping the rows where business.photo had already a 1 with this clause HAVING max(photo) = 0

Collectives™ on Stack Overflow

Update particular value in table column mysql

2 Answers 2

5 Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

5 Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related