CODEWITHSUNDEEP
sqloracle-database
Carlo
Carlo

Reputation: 11

How to populate a table with a value from another table

When populating a column from another table's column with conditions

UPDATE EVENT E 
SET TIMESTART = (SELECT TIMESTART FROM EVENTSTEPS WHERE STEPNUMBER=1) 
WHERE EVENTREF = (SELECT EVENTREF FROM EVENTSTEPS);

I am getting this error in 10g:

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

I don't know what's the right thing to do if I want to populate a table's column from other tables column. Can someone help me out on this? Thanks.

Upvotes: 0

Views: 205

Answers (3)

APC
APC

Reputation: 146219

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

The update statement requires we assign one value to the the column in the SET clause. Your sub-query returns a row for every EVENTSTEPS record where STEPNUMBER = 1. This is because you are not restricting the sub-query to return only the row for the current EVENT.

What you need is a correlated sub-query:

UPDATE EVENT E 
SET e.TIMESTART = (SELECT es.TIMESTART 
                   FROM EVENTSTEPS es
                   WHERE es.EVENTREF = e.EVENTREF
                   and es.STEPNUMBER=1) 
;

Upvotes: 1

Bob Jarvis - Слава Україні
Bob Jarvis - Слава Україні

Reputation: 50017

Use a MERGE statement:

MERGE INTO EVENT e
  USING (SELECT EVENTREF, TIMESTART
           FROM EVENTSTEPS
           WHERE STEPNUMBER = 1) s
  ON (s.EVENTREF = e.EVENTREF)
WHEN MATCHED THEN
  UPDATE
    SET e.TIMESTART = s.TIMESTART;

Best of luck.

Upvotes: 1

Rams
Rams

Reputation: 2169

If you are assigning or updating any value from subquery then it should return only one value so then it can get update.

UPDATE EVENT E SET TIMESTART = (SELECT max(TIMESTART) FROM EVENTSTEPS WHERE STEPNUMBER=1) 
WHERE EVENTREF = (SELECT max(EVENTREF) FROM EVENTSTEPS);

And in where condition that subquery returns more than one row and you want to update for all the rows which met the condition then put in instead of =

Upvotes: 0

Related Questions