Oracle update query NULL error

Question

I have 3 tables:

Table1
-------
ID
Field1
Field2


Table2
_______
ID
Name


Table2_OLD
____________
ID
Name

I need to update ID in table 2 to the ID from Table2 where Table2.Name = Table2_OLD.Name

IDs in Table2 and Table2_OLD are different, only Names are to be used to get the correct ID:

SELECT Table2.ID 
FROM Table2 
  INNER JOIN Table2_OLD ON Table2.Name=Table2_OLD.Name

I wrote the following update statement:

UPDATE Table1 SET Table1.ID=(SELECT DISTINCT t2.ID
                             FROM Table2 t2
                               INNER JOIN Table2_OLD t3 
                                       ON t2.Name=t3.Name 
                                      AND t2.ID=Table1.ID)

but it gives me an error

cannot update Table1.ID to null`

I tried using

UPDATE Table1 SET Table1.ID = (SELECT DISTINCT t2.ID
                               FROM Table2 t2
                                 INNER JOIN Table2_OLD t3 
                                         ON t2.Name=t3.Name 
                                        AND t2.ID=Table1.ID 
                                        AND NOT t2.ID is null)

but got the same error.

Can anyone help?

I think you want a `WHERE` clause with that update statement, not a subquery. — Jacob H, Jun 18 '18 at 18:25
I cannot access any values from subquery from the update statement — Coding Duchess, Jun 18 '18 at 18:34
There is no `join` keyword in the syntax of [`update`](https://docs.oracle.com/en/database/oracle/oracle-database/18/sqlrf/UPDATE.html). — William Robertson, Jun 18 '18 at 19:31
If you need to restrict the rows to be updated, then add a `where` clause to the `update`. — William Robertson, Jun 18 '18 at 22:24

Kamil Gosciminski · Answer 1 · 2018-06-18T19:26:18.853

1

Haven't tested it but a correlated update in Oracle should work:

UPDATE table1 t1
SET id = (SELECT DISTINCT t2.id
          FROM table2 t2
          JOIN table2_old t3 ON
            t2.name = t3.name
          WHERE t3.id = t1.id
          )
WHERE EXISTS (
  SELECT 1
  FROM table2 t2
  WHERE t3.id = t1.id
)

edited Jun 18 '18 at 19:26

answered Jun 18 '18 at 18:29

Kamil Gosciminski

16,547
8
49
72

I changed your query slightly, as table1 and table2 are not connected in any way: UPDATE table1 t1 SET t1.id = (SELECT t2.id FROM table2 t2 JOIN table2_old t3 ON t2.name = t3.name WHERE t3.id = t1.id ) WHERE EXISTS ( SELECT 1 FROM table2_old t3 WHERE t3.id = t1.id ) but now I am getting an error that subquery returns multiple results (ID is not unique to the table) – Coding Duchess Jun 18 '18 at 18:49
How are table1 and table2 not connected? See your condition: `AND t2.ID=Table1.ID`. You need that condition, otherwise you would get the error you got... – Kamil Gosciminski Jun 18 '18 at 19:09
Table1 and Table2 have no direct connection. Table1.ID= Table2_OLD.ID and Table2_OLD.Name=Table2.Name. And I want to update Table1.ID with Table2.ID – Coding Duchess Jun 18 '18 at 19:21
I've updated the answer. Is it OK now? – Kamil Gosciminski Jun 18 '18 at 19:26

D-Shih · Answer 2 · 2018-06-18T19:02:18.083

1

If you want to JOIN Update, You can try this.

UPDATE (
    SELECT t2.ID t2ID,t1.ID t1ID
    FROM Table1 t1
    INNER JOIN Table2_OLD t2Old ON t1.ID= t2Old.ID
    INNER JOIN Table2 t2 ON t2.Name=t2Old.Name 
    WHERE t2.ID is NOT null
) 
SET t1ID = t2ID

edited Jun 18 '18 at 19:02

answered Jun 18 '18 at 18:31

D-Shih

44,943
6
31
51

actually Table1 and Table2 have no direct connection. Table1.ID= Table2_OLD.ID and Table2_OLD.Name=Table2.Name. And I want to uodate Table1.ID with Table2.ID – Coding Duchess Jun 18 '18 at 18:56
@CodingDuchess OK,I edit my answer,If that didn't hit your expect you can provide some sample data and expect result, let the question clear :) – D-Shih Jun 18 '18 at 19:02
Now I am getting an error `cannot modify a column which maps to a non key-preserved table` – Coding Duchess Jun 18 '18 at 19:20
Right, the view needs to be key-preserved. You must be missing a unique constraint on the parent of one of the joins. https://stackoverflow.com/q/20143115/230471 – William Robertson Jun 18 '18 at 19:24

Oracle update query NULL error

2 Answers2