CODEWITHSUNDEEP
mysqlsqloracle-database
Tapeshvar
Tapeshvar

Reputation: 338

Update statement when values of two columns matches in a same table in ORACLE

How to write a query to update the status column where name ,city and qty matches in Oracle.

ID NAME  CITY    QTY   STATUS
---------- ---------------------
1 ABC    CHN      50   0
2 XYZ    MUM      20   0
3 MNO    KOL      30   0
4 ABC    CHN      50   0
5 XYZ    MUM      20   0
6 MNO    KOL      40   0

result after query execution

ID NAME  CITY    QTY   STATUS
---------- ---------------------
1 ABC    CHN      50   1
2 XYZ    MUM      20   1
3 MNO    KOL      30   0
4 ABC    CHN      50   1
5 XYZ    MUM      20   1
6 MNO    KOL      40   0

In the example given the rows 1 and 4 are having city and name equal values. Also rows 2 and 5 are similar.

Upvotes: 0

Views: 574

Answers (2)

jarlh
jarlh

Reputation: 44796

UPDATE TABLE_NAME as t_out
SET STATUS = '1'
WHERE EXISTS (SELECT 1 FROM TABLE_NAME as t_in
              WHERE t_out.NAME = t_in.NAME
                AND t_out.CITY = t_in.CITY
              GROUP BY NAME, CITY
              HAVING COUNT(*) > 1);

Upvotes: 1

Justin Cave
Justin Cave

Reputation: 231781

My guess is that you want

UPDATE table_name a
   SET status = (SELECT count(*)
                   FROM table_name b
                  WHERE a.name = b.name
                    AND a.qty  = b.qty
                    AND a.id  != b.id);

Upvotes: 2

Related Questions