CODEWITHSUNDEEP
sqloracle-databasemergesql-updateinner-join
Andrew Brown
Andrew Brown

Reputation: 11

Oracle SQL: Trying to update multiple rows from joined table without 1-1 relationship

Former SQL Server dataminer here expanding my skills. Complete newbie to Oracle. I've run into multiple error messages trying to convert this SQL query to work in Oracle:

UPDATE table1 
  SET program = SUBSTR(table2.project,1,5) 
  FROM table1 
  LEFT OUTER JOIN table2 
    ON table1.id = table2.id 
    WHERE table1.program = 'THE_PROGRAM_NAME' 
    AND table2.program = 'THE_PROGRAM_NAME';

I've dealt with each error having gone through various StackOverflow questions, but none I've encountered have helped me resolve it fully. The basic problem is that Oracle doesn't want to deal with taking one line from the joined table to update multiple lines from the primary table, and I don't know how to address this.

All I've read so far seems to indicate that this is an insurmountable problem so I'm asking my own new question to have that confirmed or refuted, and either get a whole new approach to try or that so-far-elusive solution.

This is as far as I have gotten, and it's led me to "ORA-30926 unable to get a stable set of rows in the source tables":

MERGE INTO table1 ce 
  USING 
     (SELECT DISTINCT SUBSTR(table2.project,1,5) newvalue, table1.code, table1.id 
      FROM table1 
      LEFT OUTER JOIN table2 
        ON table1.id = table2.id 
        WHERE table1.program = 'THE_PROGRAM_NAME' 
        AND table2.program = 'THE_PROGRAM_NAME'
     ) combined 
     ON (ce.id = combined.id
     AND ce.code = combined.code) 
  WHEN MATCHED THEN 
     UPDATE SET ce.program = combined.newvalue;

If you need more information to be able help please ask. Any help greatly appreciated.

Regards,

Upvotes: 1

Views: 435

Answers (1)

GMB
GMB

Reputation: 222492

Oracle does not support joins in update queries. A typical translation would use a correlated subquery:

UPDATE table1  t1
SET program = (
    SELECT SUBSTR(t2.project,1,5) 
    FROM table2 t2
    WHERE t1.id = t2.id AND t2.program = t1.program
)
WHERE program = 'THE_PROGRAM_NAME';

Note that this would update program to null if there is no match in table2. If you want to avoid that, then add a condition in the WHERE clause:

UPDATE table1  t1
SET program = (
    SELECT SUBSTR(t2.project,1,5) 
    FROM table2 t2
    WHERE t1.id = t2.id AND t2.program = t1.program
)
WHERE program = 'THE_PROGRAM_NAME' AND EXISTS (
    SELECT 1
    FROM table2 t2
    WHERE t1.id = t2.id AND t2.program = t1.program
);

Upvotes: 0

Related Questions