self join - ORA-01427: single-row subquery returns more than one row 2

[AlStl]

CREATE TABLE TEST4
(
ITEM VARCHAR2 (12),
JOBNUM VARCHAR2 (12),
GENERIC VARCHAR2 (20),
TERM_EQUIP_GROUP_ID VARCHAR2 (20),

PRIMARY KEY (ITEM, JOBNUM)
);





INSERT INTO TEST4 VALUES ('AAAA1', 'XXXX1', '1AE12.05', '1AE');
INSERT INTO TEST4 VALUES ('AAAA1', 'XXXX2', '1AE12.06', '1AE');
INSERT INTO TEST4 VALUES ('AAAA1', 'XXXX3', '1AE12.07', '1AE');

INSERT INTO TEST4 VALUES ('BBBB1', 'YYYY1', '5E8', '5ES');
INSERT INTO TEST4 VALUES ('BBBB1', 'YYYY2', '5E9', '5ES');
INSERT INTO TEST4 VALUES ('BBBB1', 'YYYY3', '5E10','5ES');
INSERT INTO TEST4 VALUES ('BBBB1', 'YYYY4', '5E11','5ES');
INSERT INTO TEST4 VALUES ('BBBB1', 'YYYY5', '5E12','5ES');
INSERT INTO TEST4 VALUES ('BBBB1', 'YYYY6', '5E13','5ES');
INSERT INTO TEST4 VALUES ('BBBB1', 'YYYY7', '5E14','5ES');


INSERT INTO TEST4 VALUES ('CCCC1', 'ZZZZ1', 'REL-18', 'DCO');
INSERT INTO TEST4 VALUES ('CCCC1', 'ZZZZ2', 'REL-20', 'DCO');
INSERT INTO TEST4 VALUES ('CCCC1', 'ZZZZ3', 'REL-21', 'DCO');
INSERT INTO TEST4 VALUES ('CCCC1', 'ZZZZ4', 'REL-22', 'DCO');
INSERT INTO TEST4 VALUES ('CCCC1', 'ZZZZ5', 'REL-23', 'DCO');
INSERT INTO TEST4 VALUES ('CCCC1', 'ZZZZ6', 'REL-24', 'DCO');
INSERT INTO TEST4 VALUES ('CCCC1', 'ZZZZ7', 'REL-25', 'DCO');

--- This update statement return ORA-01427: single-row subquery
--- returns more than one row. When i am trying to update
--- TEST4.generic based on below case statement.
--- Never used self join with update & case?

UPDATE TEST4

SET GENERIC =
(SELECT CASE WHEN A.GENERIC='1AE12.05' AND A.TERM_EQUIP_GROUP_ID ='1AE' THEN 'UNSPEC'
WHEN A.GENERIC='1AE12.06' AND A.TERM_EQUIP_GROUP_ID ='1AE' THEN 'UNSPEC'
WHEN A.GENERIC='1AE12.07' AND A.TERM_EQUIP_GROUP_ID ='1AE' THEN 'UNSPEC'
WHEN A.GENERIC='5E8' AND A.TERM_EQUIP_GROUP_ID ='5ES' THEN 'UNSPEC'
WHEN A.GENERIC='5E9' AND A.TERM_EQUIP_GROUP_ID ='5ES' THEN 'UNSPEC'
WHEN A.GENERIC='5E10' AND A.TERM_EQUIP_GROUP_ID ='5ES' THEN 'UNSPEC'
WHEN A.GENERIC='5E11' AND A.TERM_EQUIP_GROUP_ID ='5ES' THEN '5E11'
WHEN A.GENERIC='5E12' AND A.TERM_EQUIP_GROUP_ID ='5ES' THEN '5E12'
WHEN A.GENERIC='5E13' AND A.TERM_EQUIP_GROUP_ID ='5ES' THEN '5E13'
WHEN A.GENERIC='5E14' AND A.TERM_EQUIP_GROUP_ID ='5ES' THEN '5E14'
WHEN A.GENERIC='REL-18' AND A.TERM_EQUIP_GROUP_ID ='DCO' THEN 'UNSPEC'
WHEN A.GENERIC='REL-20' AND A.TERM_EQUIP_GROUP_ID ='DCO' THEN 'R20'
WHEN A.GENERIC='REL-21' AND A.TERM_EQUIP_GROUP_ID ='DCO' THEN 'R21'
WHEN A.GENERIC='REL-22' AND A.TERM_EQUIP_GROUP_ID ='DCO' THEN 'R22'
WHEN A.GENERIC='REL-23' AND A.TERM_EQUIP_GROUP_ID ='DCO' THEN 'R23'
WHEN A.GENERIC='REL-24' AND A.TERM_EQUIP_GROUP_ID ='DCO' THEN 'R24'
WHEN A.GENERIC='REL-25' AND A.TERM_EQUIP_GROUP_ID ='DCO' THEN 'R25'

ELSE NULL
END
FROM TEST4 A, TEST4 B
WHERE A.CLLI=B.CLLI AND A.JOB_ID=B.JOB_ID AND A.TERM_EQUIP_GROUP_ID = B.TERM_EQUIP_GROUP_ID)

 

[SantaMufasa]

AlStl,

Thank you very much for posting both CREATE-TABLE and INSERT statements.

Your SELECT statement, however, refers to columns, "CLLI" and "JOB_ID", which do not exist in the TEST4 table. Could you please modify your table definition appropriately, then repost?

Thanks,

Mufasa
(aka Dave of Sandy, Utah, USA)
“People may forget what you say, but they will never forget how you made them feel.

 

[carp]

AlStl -
In addition to the column name mismatch cited by SMufasa, your problem stems from your subquery. First, there is no reason for a self-join here - in fact, you don't even need the subquery. Your subquery is returning a value for all rows to a SET statement that only wants a single value - hence your error. I think the following would do what you are trying to accomplish (once the column names are set correctly):

UPDATE TEST4        

SET GENERIC =
CASE 
WHEN GENERIC='1AE12.05' AND TERM_EQUIP_GROUP_ID ='1AE' THEN 'UNSPEC'
 WHEN GENERIC='1AE12.06' AND TERM_EQUIP_GROUP_ID ='1AE' THEN 'UNSPEC'
 WHEN GENERIC='1AE12.07' AND TERM_EQUIP_GROUP_ID ='1AE' THEN 'UNSPEC'
 WHEN GENERIC='5E8' AND TERM_EQUIP_GROUP_ID ='5ES' THEN 'UNSPEC'
 WHEN GENERIC='5E9' AND TERM_EQUIP_GROUP_ID ='5ES' THEN 'UNSPEC'
 WHEN GENERIC='5E10' AND TERM_EQUIP_GROUP_ID ='5ES' THEN 'UNSPEC'
 WHEN GENERIC='5E11' AND TERM_EQUIP_GROUP_ID ='5ES' THEN '5E11'
 WHEN GENERIC='5E12' AND TERM_EQUIP_GROUP_ID ='5ES' THEN '5E12'
 WHEN GENERIC='5E13' AND TERM_EQUIP_GROUP_ID ='5ES' THEN '5E13'
 WHEN GENERIC='5E14' AND TERM_EQUIP_GROUP_ID ='5ES' THEN '5E14'
 WHEN GENERIC='REL-18' AND TERM_EQUIP_GROUP_ID ='DCO' THEN 'UNSPEC'
 WHEN GENERIC='REL-20' AND TERM_EQUIP_GROUP_ID ='DCO' THEN 'R20'
 WHEN GENERIC='REL-21' AND TERM_EQUIP_GROUP_ID ='DCO' THEN 'R21'
 WHEN GENERIC='REL-22' AND TERM_EQUIP_GROUP_ID ='DCO' THEN 'R22'
 WHEN GENERIC='REL-23' AND TERM_EQUIP_GROUP_ID ='DCO' THEN 'R23'
 WHEN GENERIC='REL-24' AND TERM_EQUIP_GROUP_ID ='DCO' THEN 'R24'
 WHEN GENERIC='REL-25' AND TERM_EQUIP_GROUP_ID ='DCO' THEN 'R25'
  ELSE NULL
END;

 

[taupirho]

You're update columns don't match with the DML statements you gave so I'm assuming that CLLI = ITEM and that JOBNUM = JOB_ID. Notwithstanding that due to you having a primary key on your table, this is an ideal scenario for the use of an updateable join view,

viz ...

SQL> select * from test4;

ITEM JOBNUM GENERIC TERM_EQUIP_GROUP_ID
------------ ------------ -------------------- --------------------
AAAA1 XXXX2 1AE12.06 1AE
AAAA1 XXXX3 1AE12.07 1AE
BBBB1 YYYY1 5E8 5ES
BBBB1 YYYY2 5E9 5ES
BBBB1 YYYY3 5E10 5ES
BBBB1 YYYY4 5E11 5ES
BBBB1 YYYY5 5E12 5ES
BBBB1 YYYY6 5E13 5ES
BBBB1 YYYY7 5E14 5ES
CCCC1 ZZZZ1 REL-18 DCO
CCCC1 ZZZZ2 REL-20 DCO

ITEM JOBNUM GENERIC TERM_EQUIP_GROUP_ID
------------ ------------ -------------------- --------------------
CCCC1 ZZZZ3 REL-21 DCO
CCCC1 ZZZZ4 REL-22 DCO
CCCC1 ZZZZ5 REL-23 DCO
CCCC1 ZZZZ6 REL-24 DCO
CCCC1 ZZZZ7 REL-25 DCO

16 rows selected.


update
(
select
CASE
WHEN A.GENERIC='1AE12.05' AND A.TERM_EQUIP_GROUP_ID ='1AE' THEN 'UNSPEC'
WHEN A.GENERIC='1AE12.06' AND A.TERM_EQUIP_GROUP_ID ='1AE' THEN 'UNSPEC'
WHEN A.GENERIC='1AE12.07' AND A.TERM_EQUIP_GROUP_ID ='1AE' THEN 'UNSPEC'
WHEN A.GENERIC='5E8' AND A.TERM_EQUIP_GROUP_ID ='5ES' THEN 'UNSPEC'
WHEN A.GENERIC='5E9' AND A.TERM_EQUIP_GROUP_ID ='5ES' THEN 'UNSPEC'
WHEN A.GENERIC='5E10' AND A.TERM_EQUIP_GROUP_ID ='5ES' THEN 'UNSPEC'
WHEN A.GENERIC='5E11' AND A.TERM_EQUIP_GROUP_ID ='5ES' THEN '5E11'
WHEN A.GENERIC='5E12' AND A.TERM_EQUIP_GROUP_ID ='5ES' THEN '5E12'
WHEN A.GENERIC='5E13' AND A.TERM_EQUIP_GROUP_ID ='5ES' THEN '5E13'
WHEN A.GENERIC='5E14' AND A.TERM_EQUIP_GROUP_ID ='5ES' THEN '5E14'
WHEN A.GENERIC='REL-18' AND A.TERM_EQUIP_GROUP_ID ='DCO' THEN 'UNSPEC'
WHEN A.GENERIC='REL-20' AND A.TERM_EQUIP_GROUP_ID ='DCO' THEN 'R20'
WHEN A.GENERIC='REL-21' AND A.TERM_EQUIP_GROUP_ID ='DCO' THEN 'R21'
WHEN A.GENERIC='REL-22' AND A.TERM_EQUIP_GROUP_ID ='DCO' THEN 'R22'
WHEN A.GENERIC='REL-23' AND A.TERM_EQUIP_GROUP_ID ='DCO' THEN 'R23'
WHEN A.GENERIC='REL-24' AND A.TERM_EQUIP_GROUP_ID ='DCO' THEN 'R24'
WHEN A.GENERIC='REL-25' AND A.TERM_EQUIP_GROUP_ID ='DCO' THEN 'R25'
ELSE NULL
END generic_new, a.generic generic_old
FROM TEST4 A, TEST4 B
WHERE A.item=B.item AND A.JOBnum=B.JOBnum AND A.TERM_EQUIP_GROUP_ID =
B.TERM_EQUIP_GROUP_ID
)
set generic_old = generic_new
SQL> /

16 rows updated.

SQL> select * from test4;

ITEM JOBNUM GENERIC TERM_EQUIP_GROUP_ID
------------ ------------ -------------------- --------------------
AAAA1 XXXX2 UNSPEC 1AE
AAAA1 XXXX3 UNSPEC 1AE
BBBB1 YYYY1 UNSPEC 5ES
BBBB1 YYYY2 UNSPEC 5ES
BBBB1 YYYY3 UNSPEC 5ES
BBBB1 YYYY4 5E11 5ES
BBBB1 YYYY5 5E12 5ES
BBBB1 YYYY6 5E13 5ES
BBBB1 YYYY7 5E14 5ES
CCCC1 ZZZZ1 UNSPEC DCO
CCCC1 ZZZZ2 R20 DCO

ITEM JOBNUM GENERIC TERM_EQUIP_GROUP_ID
------------ ------------ -------------------- --------------------
CCCC1 ZZZZ3 R21 DCO
CCCC1 ZZZZ4 R22 DCO
CCCC1 ZZZZ5 R23 DCO
CCCC1 ZZZZ6 R24 DCO
CCCC1 ZZZZ7 R25 DCO

16 rows selected.

SQL>

Is this what you were expecting as a result set ?




In order to understand recursion, you must first understand recursion.

 

[AlStl]

Mufasa,

Sorry for column mismatch. Taupirho interpreted it correctly. I will make sure this does not happen again.

CLLI = ITEM and that JOBNUM = JOB_ID

Taupirho,
Your suggested update did the job. It worked just fine.

Carp,
Thx for your idea as well.

Al