更新唯一表SQL

Question

您好我有这张桌子。

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

我需要把这张桌子改造一下。

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

我有这个QRY，但没有函数。你有一个想法吗？

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';

谢谢。

Answer 1

您需要一个子查询来设置值：

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仅在子查询中是已知的，在外部查询中是未知的。

Answer 2

下面的代码将执行您想要的操作：

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'

祝你好运。