What Is Wrong with My Update Statement with a Join in Oracle

What is wrong with my update statement with a join in Oracle?

Good question.

To simulate your situation, I've created sample tables:

SQL> create table t_debtor(id_debtor,header)
  2  as
  3  select 1, 'Header 1' from dual union all
  4  select 2, null from dual union all
  5  select 3, 'Header 3' from dual
  6  /

Tabel is aangemaakt.

SQL> create table t_element (id_element,id_debtor,insurer)
  2  as
  3  select 1, 1, 'to be updated' from dual union all
  4  select 2, 1, 'to be updated' from dual union all
  5  select 3, 2, 'not to be updated' from dual union all
  6  select 4, 2, 'not to be updated' from dual union all
  7  select 5, 3, 'to be updated' from dual
  8  /

Tabel is aangemaakt.

And with your current update statement, the problem becomes clear: the "not to be updated" values are set to NULL:

SQL> update
  2      T_ELEMENT elt
  3      set elt.INSURER = (
  4          select HEADER
  5              from T_DEBTOR debtor
  6              where
  7                  debtor.HEADER is not null
  8                  and debtor.ID_DEBTOR = elt.ID_DEBTOR)
  9  /

5 rijen zijn bijgewerkt.

SQL> select * from t_element
  2  /

ID_ELEMENT  ID_DEBTOR INSURER
---------- ---------- -----------------
         1          1 Header 1
         2          1 Header 1
         3          2
         4          2
         5          3 Header 3

5 rijen zijn geselecteerd.

The best way to do this update, is to update a join of both tables. There are some restrictions however:

SQL> rollback
  2  /

Rollback is voltooid.

SQL> update ( select elt.insurer
  2                , dtr.header
  3             from t_element elt
  4                , t_debtor dtr
  5            where elt.id_debtor = dtr.id_debtor
  6              and dtr.header is not null
  7         )
  8     set insurer = header
  9  /
   set insurer = header
       *
FOUT in regel 8:
.ORA-01779: cannot modify a column which maps to a non key-preserved table

With the bypass ujvc hint, we can circumvent this restriction.
But it is not advisable to do so unless you know really really sure that t_debtor.id_debtor is unique.

SQL> update /*+ bypass_ujvc */
  2         ( select elt.insurer
  3                , dtr.header
  4             from t_element elt
  5                , t_debtor dtr
  6            where elt.id_debtor = dtr.id_debtor
  7              and dtr.header is not null
  8         )
  9     set insurer = header
 10  /

3 rijen zijn bijgewerkt.

SQL> select * from t_element
  2  /

ID_ELEMENT  ID_DEBTOR INSURER
---------- ---------- -----------------
         1          1 Header 1
         2          1 Header 1
         3          2 not to be updated
         4          2 not to be updated
         5          3 Header 3

5 rijen zijn geselecteerd.

It's better to just add a primary key. You'll probably have this one already in place:

SQL> rollback
  2  /

Rollback is voltooid.

SQL> alter table t_debtor add primary key (id_debtor)
  2  /

Tabel is gewijzigd.

SQL> update ( select elt.insurer
  2                , dtr.header
  3             from t_element elt
  4                , t_debtor dtr
  5            where elt.id_debtor = dtr.id_debtor
  6              and dtr.header is not null
  7         )
  8     set insurer = header
  9  /

3 rijen zijn bijgewerkt.

SQL> select * from t_element
  2  /

ID_ELEMENT  ID_DEBTOR INSURER
---------- ---------- -----------------
         1          1 Header 1
         2          1 Header 1
         3          2 not to be updated
         4          2 not to be updated
         5          3 Header 3

5 rijen zijn geselecteerd.

Regards,
Rob.

Update statement with inner join on Oracle

That syntax isn't valid in Oracle. You can do this:

UPDATE table1 SET table1.value = (SELECT table2.CODE
                                  FROM table2 
                                  WHERE table1.value = table2.DESC)
WHERE table1.UPDATETYPE='blah'
AND EXISTS (SELECT table2.CODE
            FROM table2 
            WHERE table1.value = table2.DESC);

Or you might be able to do this:

UPDATE 
(SELECT table1.value as OLD, table2.CODE as NEW
 FROM table1
 INNER JOIN table2
 ON table1.value = table2.DESC
 WHERE table1.UPDATETYPE='blah'
) t
SET t.OLD = t.NEW

It depends if the inline view is considered updateable by Oracle
( To be updatable for the second statement depends on some rules listed
here ).

ORACLE SQL UPDATE with INNER JOIN isn't working

Oracle does not support this MySQL-style update join syntax. But, you may use a correlated subquery instead:

UPDATE orders AS o
SET total_price_cache = (SELECT SUM(total_price) FROM order_lines ol
                         WHERE ol.order_id = o.order_id);

SQL JOIN with UPDATE

Your join is wrong (You are using FROM in UPDATE)
This syntax for JOIN is not for ORACLE

UPDATE TBL_TEST2 
SET TBL_TEST2.NAME = 'DONE'  
FROM TBL_TEST2 
INNER JOIN TBL_TEST1 ON TBL_TEST2.DISTRICT = TBL_TEST1.DISTRICT 
WHERE TBL_TEST2.DISTRICT = '1';

In ORACLE A simple way for update joined table is based on the use the the joined select as a table

  UPDATE ( 
  SELECT TBL_TEST2.NAME AS OLD_VALUE
  FROM TBL_TEST2 
  INNER JOIN TBL_TEST1 ON TBL_TEST2.DISTRICT = TBL_TEST1.DISTRICT 
  WHERE TBL_TEST2.DISTRICT = '1' ) T
  SET T.OLD_VALUE = 'DONE'  ;

Oracle SQL : UPDATE JOIN

You can do this:

UPDATE AlfaGood ag
   SET ag.name = 'New text goes here' 
--FROM AlfaGood ag -- not an Oracle syntax
 WHERE ag.agrid = 'Thats my original text' 
   AND ag.id = 1 
   AND EXISTS ( SELECT ca.id FROM SecondAlfa ca WHERE ca.id = 1 );

SQL command not properly ended : Update SET FROM JOIN

In Oracle you do not have the JOIN clause in the UPDATE statement. You can use the following:
UPDATE T1
SET T1.IDC = (SELECT T2.IDC from T2 WHERE T2.IDO = T1.IDC)
Add the WHERE clause if you do not want to update the entire table.

SQL Update Statement using inner Join condition

You do not need the alias on the set. You are already stating that you are updating S_CONTACT. This answer is for SQL-Server as you appear to be using syntax that is product specific. This syntax isn't valid in Oracle.

UPDATE S_CONTACT
SET ACTIVE_FLG = 'N'
FROM S_CONTACT CON
JOIN S_USER USR
ON CON.PAR_ROW_ID= USR.PAR_ROW_ID
WHERE USR.LOGIN in('BJAME','GWOOD','HTRAME')

For Oracle you can try the below:

UPDATE S_CONTACT
SET S_CONTACT.ACTIVE_FLG = 'N'
WHERE S_CONTACT.PAR_ROW_ID IN
        (SELECT CON.PAR_ROW_ID
        FROM S_CONTACT CON
        INNER JOIN S_USER USR
        ON CON.PAR_ROW_ID= USR.PAR_ROW_ID
        WHERE USR.LOGIN in('BJAME','GWOOD','HTRAME'))

Update With Inner Join throwing SQL command not properly ended error

The syntax of UPDATE statement in Oracle does not support joins.

A MERGE statement can be used to do this task:

MERGE INTO DEPARTMENT d
USING (
     SELECT
             rownum as rank, age, deptId 
     FROM 
       (SELECT AVG(age) AS age, deptid 
        FROM employee
        GROUP by deptId
        ORDER BY age DESC)
) q
ON ( d.DEPTID = q.DEPTID )
WHEN MATCHED THEN UPDATE
SET d.RANK = q.rank

A working demo: http://sqlfiddle.com/#!4/f0f34/2

---

Related Topics