0

I've go this request in SQL for a migration:

UPDATE table1
SET table1.value = table2.value
FROM table1
INNER JOIN table2
ON table2.othervalue2 = table1.key
WHERE table2.othervalue3 is NULL

Works well. But not with Oracle. I know it isn't impossible to make join in a update with oracle, so I use this:

UPDATE table1 SET table1.value = (SELECT table2.value
                                  FROM table2
                                  WHERE table2.othervalue2 = table1.key
                                  AND table2.othervalue3 is NULL)
WHERE EXISTS (SELECT table2.value
                                  FROM table2
                                  WHERE table2.othervalue2 = table1.key
                                  AND table2.othervalue3 is NULL)

but I have an ORA-01427: Subquery returns more than one row.

Thanks

BaaLa
  • 31
  • 1
  • 7
  • Does this answer your question? [Update statement with inner join on Oracle](https://stackoverflow.com/questions/2446764/update-statement-with-inner-join-on-oracle) – astentx Nov 18 '22 at 08:03
  • yes I use this answer initially For the second, I have an ORA-01779 – BaaLa Nov 18 '22 at 08:08
  • Which answer? There are at least two different approaches in answers: with `update ... exists` and with `merge` – astentx Nov 18 '22 at 08:19
  • The first one. For the approach with MERGE, I have an ORA-3029 `MERGE table1 USING table2 ON table2.othervalue2 = table1.key WHEN MATCHED THEN UPDATE SET table1.value = table2.value WHERE table2.othervalue2 is NULL;` – BaaLa Nov 18 '22 at 08:21
  • 2
    Then please check [error messages](https://docs.oracle.com/en/database/oracle/oracle-database/19/errmg/ORA-29250.html#GUID-DCC3FD1C-7487-469D-99AC-074BD4EAED94) and refine join condition to remove duplicates per join key. **Cause**: *A stable set of rows could not be got because of large dml activity or a non-deterministic where clause.* **Action**: *Remove any non-deterministic where clauses and reissue the dml.* – astentx Nov 18 '22 at 08:27

1 Answers1

0

i would say you have inconsistent data. From the Errors you get i would say you have duplicates in the query specified.

SELECT table2.value
  FROM table2
  WHERE table2.othervalue2 = table1.key
    AND table2.othervalue3 is NULL

you might wanne check it.or you need to have the data better specified you want to select for Update/Merge.

Epitay
  • 56
  • 5