Pascal Ackermann Pascal Ackermann - 7 days ago 6
SQL Question

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?

Answer

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;