Update statement with inner join on Oracle

Question

I have a query which works fine in MySQL, but when I run it on Oracle I get the following error:

SQL Error: ORA-00933: SQL command not properly ended
00933. 00000 - "SQL command not properly ended"

The query is:

UPDATE table1
INNER JOIN table2 ON table1.value = table2.DESC
SET table1.value = table2.CODE
WHERE table1.UPDATETYPE='blah';

Answer 1

Oracle Database 23ai has added support for direct joins in update and delete:

select employee_id, salary from hr.employees 
where  job_id = 'ST_MAN';

EMPLOYEE_ID     SALARY
----------- ----------
        120       8000
        121       8200
        122       7900
        123       6500
        124       5800

update hr.employees e
set    salary = max_salary 
from   hr.jobs j
where  j.job_id = e.job_id;

select employee_id, salary from hr.employees 
where  job_id = 'ST_MAN';

EMPLOYEE_ID     SALARY
----------- ----------
        120       8500
        121       8500
        122       8500
        123       8500
        124       8500

Answer 2

Oracle base has a good run down on this.

https://oracle-base.com/articles/misc/updates-based-on-queries

From this link - I used a modification of the above query which did not work for me (the answer from mathguy which uses rowid)

MERGE /*+ APPEND PARALLEL(8) */ INTO dest_table tt
USING source_table st
ON (tt.identifier = st.identifier)
WHEN MATCHED THEN
  UPDATE SET tt.number = st.number;

Here I have two tables: source and dest. They both have a varchar field in common and I am adding the source identify field (PK) into the dest table.

Answer 3

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 ).

Answer 4

MERGE with WHERE clause:

MERGE into table1
USING table2
ON (table1.id = table2.id)
WHEN MATCHED THEN UPDATE SET table1.startdate = table2.start_date
WHERE table1.startdate > table2.start_date;

You need the WHERE clause because columns referenced in the ON clause cannot be updated.

Answer 5

Just as a matter of completeness, and because we're talking Oracle, this could do it as well:

declare
begin
  for sel in (
    select table2.code, table2.desc
    from table1
    join table2 on table1.value = table2.desc
    where table1.updatetype = 'blah'
  ) loop
    update table1 
    set table1.value = sel.code
    where table1.updatetype = 'blah' and table1.value = sel.desc;    
  end loop;
end;
/

Answer 6

Use this:

MERGE
INTO    table1 trg
USING   (
        SELECT  t1.rowid AS rid, t2.code
        FROM    table1 t1
        JOIN    table2 t2
        ON      table1.value = table2.DESC
        WHERE   table1.UPDATETYPE='blah'
        ) src
ON      (trg.rowid = src.rid)
WHEN MATCHED THEN UPDATE
    SET trg.value = code;

Answer 7

update table1  a 
   set a.col1='Y' 
 where exists(select 1 
                from table2 b
               where a.col1=b.col1 
                 and a.col2=b.col2
             )

Answer 8

UPDATE (SELECT T.FIELD A, S.FIELD B
FROM TABLE_T T INNER JOIN TABLE_S S
ON T.ID = S.ID)
SET B = A;

A and B are alias fields, you do not need to point the table.

Answer 9

Do not use some of the answers above.

Some suggest the use of nested SELECT, don't do that, it is excruciatingly slow. If you have lots of records to update, use join, so something like:

update (select bonus 
        from employee_bonus b 
        inner join employees e on b.employee_id = e.employee_id 
        where e.bonus_eligible = 'N') t
set t.bonus = 0;

See this link for more details. http://geekswithblogs.net/WillSmith/archive/2008/06/18/oracle-update-with-join-again.aspx.

Also, ensure that there are primary keys on all the tables you are joining.

Answer 10

This following syntax works for me.

UPDATE
(SELECT A.utl_id,
    b.utl1_id
    FROM trb_pi_joint A
    JOIN trb_tpr B
    ON A.tp_id=B.tp_id Where A.pij_type=2 and a.utl_id is null
)
SET utl_id=utl1_id;

Answer 11

As indicated here, the general syntax for the first solution proposed by Tony Andrews is :

update some_table s
set   (s.col1, s.col2) = (select x.col1, x.col2
                          from   other_table x
                          where  x.key_value = s.key_value
                         )
where exists             (select 1
                          from   other_table x
                          where  x.key_value = s.key_value
                         )

I think this is interesting especially if you want update more than one field.

Answer 12

UPDATE IP_ADMISSION_REQUEST ip1
SET IP1.WRIST_BAND_PRINT_STATUS=0
WHERE IP1.IP_ADM_REQ_ID        =
  (SELECT IP.IP_ADM_REQ_ID
  FROM IP_ADMISSION_REQUEST ip
  INNER JOIN VISIT v
  ON ip.ip_visit_id=v.visit_id
  AND v.pat_id     =3702
  ); `enter code here`

Answer 13

It works fine oracle

merge into table1 t1
using (select * from table2) t2
on (t1.empid = t2.empid)
when matched then update set t1.salary = t2.salary

Answer 14

UPDATE table1 t1
SET t1.value = 
    (select t2.CODE from table2 t2 
     where t1.value = t2.DESC) 
WHERE t1.UPDATETYPE='blah';

Answer 15

 UPDATE ( SELECT t1.value, t2.CODE
          FROM table1 t1
          INNER JOIN table2 t2 ON t1.Value = t2.DESC
          WHERE t1.UPDATETYPE='blah')
 SET t1.Value= t2.CODE

Answer 16

Using description instead of desc for table2,

update
  table1
set
  value = (select code from table2 where description = table1.value)
where
  exists (select 1 from table2 where description = table1.value)
  and
  table1.updatetype = 'blah'
;