CODEWITHSUNDEEP
sqloracle-databaseselect
Pascal
Pascal

Reputation: 1315

SQL update statement with select from own table with multiple rows in oracle

I want to select rows multiple rows from a table with a WHERE clause, afterwords I want to format the date and overwrite the column. I created the following SQL statement:

UPDATE aufgabenliste.aufgabendefinition
SET tagesauswahl = (SELECT TO_CHAR(TO_DATE(CONCAT(tagesauswahl,'.2010'), 'DDD.MM.YYYY') , 'DDD.MM')  
                    FROM aufgabenliste.aufgabendefinition 
                    WHERE aufgabentyp = 4)
WHERE aufgabentyp = 4;

I get an error called:

SQL Error: ORA-01427: single-row subquery returns more than one row

How can I do a Update with a Select for multiple rows for the same table using oracle?

Upvotes: 1

Views: 680

Answers (4)

XING
XING

Reputation: 9886

The error SQL Error: ORA-01427: single-row subquery returns more than one row suggests that your inner subquery :

SELECT TO_CHAR(TO_DATE(CONCAT(tagesauswahl,'.2010'), 'DDD.MM.YYYY') , 'DDD.MM')  
                    FROM aufgabenliste.aufgabendefinition 
                    WHERE aufgabentyp = 4)

is retruning multiple rows and you cannot assign multiple records to single column without using loop, hence your update is failing. The solution to your update is either you restrict the number of rows to 1 or do something that the inner subquery results to a single row.

You can use listagg to convert multiple rows to a single row seperated by a delimeter and then update your table. See below:

UPDATE aufgabenliste.aufgabendefinition
SET tagesauswahl = (SELECT listagg( TO_CHAR(TO_DATE(CONCAT(tagesauswahl,'.2010'), 'DDD.MM.YYYY') , 'DDD.MM'),',') within group (order by 1)  
                    FROM aufgabenliste.aufgabendefinition 
                    WHERE aufgabentyp = 4)
WHERE aufgabentyp = 4;

Note that, listagg has some limitations and if the string length increases the limit of a varchar, it would fail. But if you have limited number of rows, it would work.

Upvotes: 1

SUNIL KUMAR
SUNIL KUMAR

Reputation: 135

UPDATE aufgabenliste.aufgabendefinition arow
SET tagesauswahl = (SELECT TO_CHAR(TO_DATE(CONCAT(tagesauswahl,'.2010'), 'DDD.MM.YYYY') , 'DDD.MM')  
                    FROM aufgabenliste.aufgabendefinition 
                    WHERE ROWID = arow.ROWID)
WHERE aufgabentyp = 4;

You can update using co-related sub queries.
arow is alias for the table getting update. In the sub query we are referring to the specific row getting updated using unique ROWID given to every record by Oracle.

Upvotes: 2

Mansoor
Mansoor

Reputation: 4192

UPDATE aufgabenliste.aufgabendefinition SET tagesauswahl = TO_CHAR(TO_DATE(CONCAT(tagesauswahl,'.2010'), 'DDD.MM.YYYY') , 'DDD.MM') 
WHERE aufgabentyp = 4;

Upvotes: 1

Jaydip Jadhav
Jaydip Jadhav

Reputation: 12309

Try this : Before performing this operation take back of your Table. I am pretty much sure about my solution but don't want to take risk :)

UPDATE aufgabenliste.aufgabendefinition
SET tagesauswahl = TO_CHAR(TO_DATE(CONCAT(tagesauswahl,'.2010'), 'DDD.MM.YYYY') , 'DDD.MM')  
WHERE aufgabentyp = 4;

Upvotes: 2

Related Questions