CODEWITHSUNDEEP
sqloracle-database
user3251591
user3251591

Reputation: 11

Oracle Update statement with ORA-00913 error

I am in the process of updating two columns in a table called "sample" with values 5, if these conditions satisfies;

1) If TABLE A.MEM_NBR = TABLE B.MEM_NBR AND TABLE B.M_ID = XXXX, AND TABLE B.CLCL = 1 and finally using a join condition like TABLE A.ID = SAMPLE.ID
2) If these conditions do not suffice, then we need not update the table.

But I am getting an error; 

SQL Error: ORA-00913: too many values 
00913. 00000 - "too many values".

Need some help on the above update script, as to where i am going wrong.

The script that i am using is as follows; 

UPDATE SAMPLE 
SET SAMPLE.PCB = 5, SAMPLE.PCBP_Q = 5 
WHERE (
    SELECT * 
    FROM MEMBER_A A,S_NEW B 
    WHERE A.MEM_NBR = B.MEM_NBR 
        AND B.M_ID = 1111 
        AND B.CLCL = 1 
        AND B.ID = C.ID);

Upvotes: 1

Views: 1019

Answers (2)

mechanical_meat
mechanical_meat

Reputation: 169374

The problem is that your subquery can return more than one value. To get around this problem you can use the EXISTS keyword. (I also converted the join to ANSI syntax.)

UPDATE SAMPLE C
SET C.PCB = 5, C.PCBP_Q = 5 
WHERE EXISTS (
    SELECT 1 
    FROM 
        MEMBER_A A
        JOIN S_NEW B 
        ON A.MEM_NBR = B.MEM_NBR 
        AND B.M_ID = 1111 
        AND B.CLCL = 1 
    WHERE 
        A.ID = C.ID);

Upvotes: 1

Vignesh Kumar A
Vignesh Kumar A

Reputation: 28413

Your where condition might be wrong. It lead to match many values without mapping

Try like this

UPDATE SAMPLE 
SET SAMPLE.PCB = 5, SAMPLE.PCBP_Q = 5 
WHERE ColumnNmae IN (
    SELECT ColumnName 
    FROM MEMBER_A A,S_NEW B 
    WHERE A.MEM_NBR = B.MEM_NBR 
        AND B.M_ID = 1111 
        AND B.CLCL = 1 
        AND B.ID = C.ID);

Or use a correlated subquery like this:

UPDATE SAMPLE 
SET SAMPLE.PCB = 5, SAMPLE.PCBP_Q = 5 
WHERE EXISTS (
    SELECT 1
    FROM MEMBER_A A,S_NEW B 
    WHERE A.MEM_NBR = B.MEM_NBR 
        AND B.M_ID = 1111 
        AND B.CLCL = 1 
        AND B.ID = C.ID
        AND A.ColumnNmae = SAMPLE.ColumnNmaes);

Upvotes: 0

Related Questions