How to rectify too many values in subquery sql

Question

UPDATE PRE_LB 
SET PR_NPR = 'PR' 
WHERE CIFNO IN (SELECT CIFNO, SUM(LIMIT) 
                FROM PRE_LB 
                WHERE LTWPB IN ('SE','ED') 
                GROUP BY CIFNO 
                HAVING SUM(LIMIT) <= 2000000)
  AND LTWPB IN ('SE', 'ED');

SQL Error: ORA-00913: too many values

How to rectify without using view?

Answer 1

To resolve this issue with the IN operator, ensure that the subquery selects only the CIFNO column :

UPDATE PRE_LB 
SET PR_NPR='PR' 
WHERE CIFNO IN (
      SELECT CIFNO
      FROM PRE_LB 
      WHERE LTWPB IN ('SE','ED') 
      GROUP BY CIFNO 
      HAVING SUM(LIMIT)<=2000000
) AND LTWPB IN ('SE','ED');