2

I want to run update statement on Oracle 11g, but it fails with the following error:

SQL Error: ORA-01427: single-row subquery returns more than one row 01427. 00000 - "single-row subquery returns more than one row"

Here's what I'm trying to do in pseudocode:

Update Table1
Set Flag_Column=1
Where
Table1.ColumnX=Table2.ColumnX
and
Table1.ColumnY=Table2.ColumunY

Is there a way to do this in Oracle SQL?

My code looks like this:

update Table1
set Flag_Column=1
where ColumnX=
    (select distinct Table1.ColumnX
    from Table1, Table2
    where Table1.ColumnX=Table2.ColumnX
    and Table1.ColumnY=Table2.ColumnY)
and ColumnY=
    (select distinct Table1.ColumnY
    from Table1, Table2
    where Table1.ColumnX=Table2.ColumnX
    and Table1.ColumnY=Table2.ColumnY)

1 Answer 1

2
update table1 set flag_column = 1
 where exists (select 1 from table2
                where table1.columnx = table2.columnx
                  and table1.columny = table2.columny)

This will update every row in table 1 where the x, y column combination exists in table2.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.