ORA-01427 Subquery returns more then one row..Oracle Update Statement

0
votes

How do you write a update statement with a Sub-Select in an Oracle Environment (SQL Developer)?

Example: UPDATE table SET column = (SELECT....)

Every time I try this it gives me ORA-01427 "Sub select returns more then one row" even if there is no WHERE clause..

3
sqloracle
Easy, just make sure that your query only return a single record. Post the full query for better results here. – Tim Biegeleisen
Can you not update multiple rows at a time..? say if I had 100,000 records that were 1's and i needed them to be 0's can I not do that in oracle with a simple update? or can I only update 1 row? – user9540900
Yes, you can. I meant that each record which is being updated must generate a subquery also returning one record. You need a correlated subquery here. – Tim Biegeleisen
finally got it.. Thank you for your help! Looked up correlated sub query and found an example that worked with my current situation!.. Thanks very much been working on it for almost a week now and couldn't figure it out. – user9540900
Take the tour, read How to Ask, and minimal reproducible example. – jwdonahue

3 Answers

1
votes

Based on the understanding of your question I'd suggest use Merge statement.

Merge into Table1
Using
(SELECT * from table2 where condition) Temp
On (Table1.columname condition Temp.columname)
When matched Then update Set Table1.column_name = Temp.column_name;

Table1 is the table where you want to update the records. Table2 is the table from which you want to get the data (The sub query which you are talking about ) Using this merge statement you will be able to update n number of rows.

0
votes

If you want to update multiple rows, you can either use a MERGE statement (as in @jackkds7's answer above) or you can use a filter on your subselect:

UPDATE table t1
   SET column = ( SELECT column FROM table2 t2 WHERE t2.key = t1.key );

If there aren't matches in table2 for all the records in table then column will be set to NULL for the non-matches. To avoid that, add a WHERE EXISTS clause:

UPDATE table t1
   SET column = ( SELECT column FROM table2 t2 WHERE t2.key = t1.key )
 WHERE EXISTS ( SELECT 1 FROM table2 t2 WHERE t2.key = t1.key );

Oh and in the event that key is not unique for table2, you can aggregate (up to you to figure out which function would be best):

UPDATE table t1
   SET column = ( SELECT MAX(column) FROM table2 t2 WHERE t2.key = t1.key )
 WHERE EXISTS ( SELECT 1 FROM table2 t2 WHERE t2.key = t1.key );

Hope this helps.

0
votes

I think it would help if you posted your actual query. In essence, the "inner" select would be executed for each row that would be updated. This inner select query is called a correlated subquery:

    UPDATE table t SET t.column = (
        select ot.othercolumn  from othertable ot 
        where ot.fk = t.id  --This is the correlation part, that finds 
        --he right value for the row you are currently updating
     )

You must ensure the subquery you use will always return just a single row and a single column for every time it runs (that is, for every row that is going to be updated). If needed, you can use MAX(), or ROWNUM to ensure you always only get 1 value

More examples: Using Correlated Subqueries