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';

2

15 Answers

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

12

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;

9

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.

1

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

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

1

 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

0

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.

1

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

2

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;

1

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'
;

1

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

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.

3

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`

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;
/

1

Oracle base has a good run down on this.

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.

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