Updating Oracle Table from result set of another query

Question

Description : The first table you see on the image needs to be updated from the eventdescription column on the second image.

I've tried using

UPDATE currentEvent 
 SET eventdescription = ( SELECT eh.eventDescription
                        FROM eventHistory eh
                        WHERE updateTime = (SELECT MAX (updateTime)
                                                FROM eventHistory
                                                WHERE eventID = eh.eventID)
                        )

and gives me this error

ORA-01427: single-row subquery returns more than one row ORA-06512: at 
"SYS.DBMS_SQL", line 1721

Is there something I am missing , Please advise.

It means that you are returning more than one entry with the value of MAX (updateTime) — Ryan Wilson, Mar 14 '18 at 18:10
You are trying to update a single field with multiple values. Just do an inner join update. https://stackoverflow.com/questions/7664312/oracle-update-statement-with-an-inner-join — dfundako, Mar 14 '18 at 18:10

Lukasz Szozda · Accepted Answer · 2018-03-14T18:25:11.663

1

You could use ORDER BY FETCH FIRST:

UPDATE currentEvent 
 SET eventdescription = (SELECT eh.eventDescription
                         FROM eventHistory eh
                         WHERE currentEvent.EventId = eh.EventId
                         ORDER BY updateTime DESC
                         FETCH FIRST 1 ROW ONLY
                        )
WHERE eventdescription IS NULL;

edited Mar 14 '18 at 18:25

answered Mar 14 '18 at 18:10

Lukasz Szozda

162,964
23
234
275

1

Works like a charm. Thankyou – sai Mar 14 '18 at 18:16
I tried adding more event_id's to the first table to see if the update can scale up. The updates still happen but, the rows which already have values under the event description are being updated too. Edit -- Is there a way to restrict the update if the table's column has prior values? – sai Mar 14 '18 at 18:24
@sai You need to add `WHERE` clause. – Lukasz Szozda Mar 14 '18 at 18:25
I added a new event_id and added the where clause with a "Is null on currentevent.eventdescription" which set all the other rows but the new event_id to "-" . Apologies, I'm new to sql. – sai Mar 14 '18 at 18:35

Updating Oracle Table from result set of another query

1 Answers1