Update field1.table1 based on compound join between table1 and table2 – Oracle

dmloracleoracle-11g-r2update

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)

Best Answer

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.

Related Solutions

Sub query with not equal

I assume you want rows from table 1 that does not exist in table_2 and vice versa:

SELECT col1, 'table_1' as origin
FROM table_1 x
WHERE NOT EXISTS (
    SELECT 1 FROM table_2 y
    WHERE x.col1 = y.cola
)
UNION
SELECT cola, 'table_2' as origin
FROM table_2 x
WHERE NOT EXISTS (
    SELECT 1 FROM table_1 y
    WHERE y.col1 = x.cola
)

Oracle – Lookup Multiple IDs in Separate Table with 2 Subqueries

You're permitted to do multiple joins in a single statement. You can do this even when joining on the same table by using aliases.

I created a very quick example which is similar to your case. I just used ids instead of a time, and I stored the whole name as a single field; you'll just have to concatenate the first and last name on your own ;)

Schema and Data

CREATE TABLE prtimesheet (
  id numeric,
  submitter_id numeric,
  approver_id numeric
);

 CREATE TABLE srm_resources (
   person_id numeric,
   name text
 );

 INSERT INTO prtimesheet VALUES(1, 1000, 1001), (2,1000,1002), (3,1002, 1001), (4,1001,1002);

 INSERT INTO srm_resources VALUES (1000,'Jack Ryan'), (1001, 'Bart Mancuso'), (1002, 'James Greer');

Multiple joins on same table using aliases

SELECT prtimesheet.id, sub_table.name AS submitter_name, app_table.name AS approver_name
FROM prtimesheet
JOIN srm_resources AS sub_table
ON prtimesheet.submitter_id = sub_table.person_id
JOIN srm_resources AS app_table
ON prtimesheet.approver_id = app_table.person_id
ORDER BY id;

This should fix you right up!

Here is a SQL Fiddle for you to play around with if you like.

Edit: And as suggested...

The Oracle version: Schema and Data

CREATE TABLE prtimesheet (
  id number,
  submitter_id number,
  approver_id numeric
 );

  CREATE TABLE srm_resources (
     person_id number,
     name varchar(25)
  );

  INSERT ALL
  INTO prtimesheet(id, submitter_id, approver_id) VALUES(1, 1000, 1001) 
  INTO prtimesheet(id, submitter_id, approver_id) VALUES(2,1000,1002)
  INTO prtimesheet(id, submitter_id, approver_id) VALUES(3,1002, 1001)
  INTO prtimesheet(id, submitter_id, approver_id) VALUES(4,1001,1002)
  SELECT * FROM DUAL;

 INSERT  ALL
 INTO srm_resources(person_id, name) VALUES(1000,'Jack Ryan') 
 INTO srm_resources(person_id, name) VALUES(1001, 'Bart Mancuso')
 INTO srm_resources(person_id, name) VALUES(1002, 'James Greer')
 SELECT * FROM DUAL;

The Oracle version: Joins and aliases

SELECT prtimesheet.id, sub_table.name AS submitter_name, app_table.name AS approver_name
FROM prtimesheet
JOIN srm_resources sub_table
ON prtimesheet.submitter_id = sub_table.person_id
JOIN srm_resources app_table
ON prtimesheet.approver_id = app_table.person_id
ORDER BY id;

Here is the Oracle SQL Fiddle!