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

Question

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?

It is not good practice to store date as a varchar2. If you can it is better to add new column with date type, fill it based on current column and then drop old column. — Kacper
– Kacper, Commented Nov 30, 2016 at 9:21
I agree with you but it's an existing application with an existing database with a big amount of data and I just have to migrate this row. So there is no other way possible, that to work with this varchar2 column. — Pascal
– Pascal, Commented Nov 30, 2016 at 9:26
Please provide some example data in your table both before and after the update, so we can try and see what it is you're wanting to do. On the face of it, @JaydipJ's answer looks to be what you're trying to do, except it seems the data in the tagesauswahl column is a string already in DDD.MM format, so why you'd want to update it to what it is already is anyone's guess. This makes me think that maybe you're trying to update it to some other date, but without the sample input/output data it's difficult to say. — Boneist
– Boneist, Commented Nov 30, 2016 at 9:27
You're right but the old format is for example 1.1, I want to migrate it to 001.01. of course there's no change for 365.12, but for the single character months and days there is a change. — Pascal
– Pascal, Commented Nov 30, 2016 at 9:29

Jaydip Jadhav · Accepted Answer · 2016-11-30 09:05:18Z

2

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;

answered Nov 30, 2016 at 9:05

Jaydip Jadhav

12.3k6 gold badges26 silver badges41 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

SUNIL KUMAR · Accepted Answer · 2016-11-30 09:18:23Z

2

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.

answered Nov 30, 2016 at 9:18

SUNIL KUMAR

1356 bronze badges

Comments

Mansoor · Accepted Answer · 2016-11-30 09:06:01Z

1

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

answered Nov 30, 2016 at 9:06

Mansoor

4,1921 gold badge19 silver badges27 bronze badges

Comments

XING · Accepted Answer · 2016-11-30 09:29:51Z

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.

Collectives™ on Stack Overflow

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

4 Answers 4

Comments

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

Comments

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related