Updating all the record for a column in a table with value from another table

Hi I want to update all the values of RequestId column of IIL_CHANGE_REQUEST Table with the RequestId of TABLE_NAME_4MINS Table where REQUESTBY column in both tables are same. I am trying to do this in oracle daatabalse(sql developer)

My query:

Update IIL_CHANGE_REQUEST 
set REQUESTID=(SELECT TABLE_NAME_4MINS.REQUESTID
                     FROM TABLE_NAME_4MINS
                     WHERE IIL_CHANGE_REQUEST.REQUESTBY = TABLE_NAME_4MINS.REQUESTBY) 
WHERE EXISTS (SELECT TABLE_NAME_4MINS.REQUESTID
                     FROM TABLE_NAME_4MINS
                     WHERE IIL_CHANGE_REQUEST.REQUESTBY = TABLE_NAME_4MINS.REQUESTBY)

But every time I do this I get an error saying:

Error starting at line : 1 in command -
Update IIL_CHANGE_REQUEST 
set REQUESTID=(SELECT TABLE_NAME_4MINS.REQUESTID
                     FROM TABLE_NAME_4MINS
                     WHERE IIL_CHANGE_REQUEST.REQUESTBY = TABLE_NAME_4MINS.REQUESTBY) 
WHERE EXISTS (SELECT TABLE_NAME_4MINS.REQUESTID
                     FROM TABLE_NAME_4MINS
                     WHERE IIL_CHANGE_REQUEST.REQUESTBY = TABLE_NAME_4MINS.REQUESTBY)

Error report –

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

Please anyone help how can I do it.

Answer

It depends on what you want to do in such cases. If you don’t really care, take any value, for example minimum:

update iil_change_request a
set a.requestid = (select min(b.requestid)            --> here
                   from table_name_4mins b
                   where a.requestby = b.requestby) 
where exists (select c.requestid
              from table_name_4mins c
              where a.requestby = c.requestby);

If that’s not what you want, then you’ll have to figure out what to do with those “duplicates”. Perhaps you’ll have to include yet another WHERE condition, or fix data, or … who knows? I don’t, while you should.