CODEWITHSUNDEEP
phpmysql
Anil Kumar Reddy
Anil Kumar Reddy

Reputation: 102

MySQL - Update Column if all rows are equal

I have N Rows with same SlNo but different RowNo as shown below for eg:

SlNo  RowNo  Status
 1     1     Opened
 1     2     Closed
 1     3     Opened
 1     4     Closed
 1     5     Opened

If all the Status Column Rows are Closed , I want to return 1.

else o.

Thanks in advance.

Upvotes: 0

Views: 144

Answers (3)

Ullas
Ullas

Reputation: 11556

Find the count of all rows and check it with the count of Closed values.

Query

select t.`SlNo`,
case when t.`RowNo_Count` = t.`closed_count` then 1 else 0 end as `new_col` 
from(
    select `SlNo`, 
    count(`RowNo`) as `RowNo_Count`,
    sum(case `Status` when 'Closed' then 1 else 0 end) as `closed_count`
    from `your_table_name`
    group by `SlNo`
)t;

Upvotes: 0

Ishwar Khairnar
Ishwar Khairnar

Reputation: 11

Update my_table set Status='Closed' where SlNo = 1

Upvotes: 0

Tarun
Tarun

Reputation: 3165

You can do it following:

SELECT STATUS FROM `table_1` where SLNO = 1 group by status

If you get only one record with value "Closed" then execute the next query

UPDATE `table_2` SET Ref_Status = 'Closed' WHERE SLNO = 1;

Upvotes: 2

Related Questions