CODEWITHSUNDEEP
sqloracle-databaseoracle11g
Ragav
Ragav

Reputation: 229

how to Update a column with specific values found in any of the rows

My query return below results, whenever i see value "YES" for COLUMN4 values i need to Set the "YES" value for all the records under the COLUMN1 group. for Example DAVID has 2 records with NO and YES- but my target state should be "YES" for all rows because he has Value "YES" for atleast one of the records.

QUery Results

Column1  Column2 Column3 Column4
=================================
Mary     AA      AAA     YES
Mary     BB      BBB     YES
David    AA      AAA     YES
David    BB      BBB     NO
Clara    AA      AAA     NO
Clara    BB      BBB     NO

Requested Target State

Column1     Column2       Column3      Column4
================================================
Mary        AA            AAA          YES
Mary        BB            BBB          YES
David       AA            AAA          YES
David       BB            BBB          **YES**
Clara       AA            AAA          NO
Clara       BB            BBB          NO

Upvotes: 0

Views: 48

Answers (3)

Aung Myo Linn
Aung Myo Linn

Reputation: 2890

Here is the Select statement you want,

SELECT mt.Column1,mt.Column2,mt.Column3,
(CASE WHEN mt2.cc IS NULL THEN 'NO' ELSE 'YES' END) AS Column4 
FROM mytable mt LEFT JOIN 
(SELECT Column1,COUNT(*) AS cc FROM mytable WHERE Column4 = 'YES' GROUP BY Column1)
AS mt2 ON mt2.Column1 = mt.Column1

Result:

+-------------+-------------+-------------+-------------+
|   Column1   |  Column2    |   Column3   |   Column4   |
+-------------+-------------+-------------+-------------+
|   Mary      |      AA     |     AAA     |      YES    |
+-------------+-------------+-------------+-------------+
|   Mary      |      BB     |     BBB     |      YES    |
+-------------+-------------+-------------+-------------+
|   David     |      AA     |     AAA     |      YES    |
+-------------+-------------+-------------+-------------+
|   David     |      BB     |     BBB     |      YES    |
+-------------+-------------+-------------+-------------+
|   Clara     |      AA     |     AAA     |      NO     |
+-------------+-------------+-------------+-------------+
|   Clara     |      BB     |     BBB     |      NO     |
+-------------+-------------+-------------+-------------+

Upvotes: 1

Mohsen Heydari
Mohsen Heydari

Reputation: 7284

Due to comments:

UPDATE my_table m1 set m.column4='YES' 
WHERE m1.column4='NO'
AND m1.column1  IN (
select m2.column1 from my_table m2 where mm.column4 = 'YES')

Upvotes: 1

Frank Ockenfuss
Frank Ockenfuss

Reputation: 2043

UPDATE my_table m set m.column4='YES' 
WHERE m.column4='NO'
AND exists(
select 1 from my_table mm where mm.column1= m.column1 and mm.column4 = 'YES')

Upvotes: 2

Related Questions