CODEWITHSUNDEEP
mysql
NoviceMe
NoviceMe

Reputation: 3256

Update particular value in table column mysql

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.

Upvotes: 0

Views: 772

Answers (2)

JCalcines
JCalcines

Reputation: 1286

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

Upvotes: 1

M Khalid Junaid
M Khalid Junaid

Reputation: 64476

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

Upvotes: 1

Related Questions