CODEWITHSUNDEEP
sqloracle-databaseexists
Santiago Espitia
Santiago Espitia

Reputation: 1

Update unique table SQL

Hello i have this table. 

ADDR      | STATE  |  ID 
CRA.20 15 |  REP   |  (null)
CRA.20 15 |  REP   |  (null)
CRA.20 15 |  REP   |   (null)
CRA.20 15 |  PRI   |   RR_88_JK

I need transform this table ..

ADDR      | STATE |   ID 
CRA.20 15 |  REP  |   RR_88_JK
CRA.20 15 |  REP  |   RR_88_JK
CRA.20 15 |  REP  |   RR_88_JK
CRA.20 15 |  PRI  |   RR_88_JK

I have this QRY but not function. You have a one idea?.

UPDATE TABLE_A A
   SET a.ID = b.ID
 WHERE EXISTS
           (SELECT b.ID
              FROM TABLE_A B
             WHERE a.ADDR = B.ADDR  AND b.STATE = 'PRI')
 and  A.STATE = 'REP';

Thanks.

Upvotes: 0

Views: 41

Answers (2)

Bob Jarvis - Слава Україні
Bob Jarvis - Слава Україні

Reputation: 50017

The following will do what you want:

UPDATE TABLE_A A
   SET a.ID = (SELECT MIN(ID)
                 FROM TABLE_A
                 WHERE ID IS NOT NULL AND
                       STATE = 'PRI')
   WHERE ID IS NULL AND
         STATE = 'REP'

Best of luck.

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269953

You need a subquery to set the value:

UPDATE TABLE_A A
   SET a.ID = (SELECT b.ID
               FROM TABLE_A B
               WHERE a.ADDR = B.ADDR AND b.STATE = 'PRI' AND rownum = 1
              )
   WHERE EXISTS (SELECT b.ID
                 FROM TABLE_A B
                 WHERE a.ADDR = B.ADDR AND b.STATE = 'PRI'
                ) AND
         A.STATE = 'REP';

B is only known in the subquery, not in the outer query.

Upvotes: 1

Related Questions