Reputation:
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..
Upvotes: 0
Views: 1394
Reputation: 176
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
Upvotes: 0
Reputation: 12485
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.
Upvotes: 0
Reputation: 142
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.
Upvotes: 1