387

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';
Improve this question
2
  • 1
    When I tried to setup table2 in Oracle to test my answer I found that Oracle rejected DESC as a column name. Commented Mar 15, 2010 at 11:57
  • Sorry I just abbreviated the original column name to desc its obviously not that in the db Commented Mar 15, 2010 at 13:27

16 Answers 16

Reset to default
534

That syntax isn't valid in Oracle. Prior to Oracle 23C 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 ).

NEW From Oracle 23C, this syntax is allowed:

UPDATE table1 SET table1.value = table2.CODE FROM table2 WHERE table1.value = table2.DESC AND table1.UPDATETYPE='blah';
Improve this answer
Sign up to request clarification or add additional context in comments.

13 Comments

I did the second example but had to add aliases to the column names in the select and then reference them by their names in the SET but it worked, thanks
The second example has the benefit of allowing you to test the SQL before actually performing the update.
The second example worked for me. I like that one because it looks clean and readable. Don't know what the pros and cons are between the two when it comes to performance. But, I wasn't worried about that for now 'cuz I used this for a one off script to correct bad data.
Explanation on key-preserved requirement for updatable joins: asktom.oracle.com/pls/asktom/…
Got this - "ORA-01779: cannot modify a column which maps to a non key-preserved table", and then stackoverflow.com/questions/9335536/… helped.
|
245

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;
Improve this answer

9 Comments

Works perfectly but Oracle required me to say merge into table 1 t and so forth.
Late to the party, but this is still a good thread. I need to know, tho'... did I miss something? Master table, "table1". In the USING, table1 aliased as t1. Table2, aliased as t2, but in the ON, the references are... ? External Table1 - not t1 - is this a reference to the outer table or a type? Table2? Not t2? Je suis confused. Fan of better aliases...
Just a point here, if your key (trg.rowid or src.rid) has one duplicated item this clause throw an error: ora-30926.ora-code.com
@Marc In the ON, trg is the alias for the master table, table1 ("outer" table by your logic), and src references the USING group ("inner table" by your logic). But yeah, probably could've been referenced better, but I was able to follow it.
@supernova: tony's answer is updating an inline view. This can work in some cases, but the view has to be "key-preserved" (every joined table has to be equality-joined on its primary key or otherwise unique fieldset). This makes sure every record in the target table contributes to at most one record in the resulting rowset, and, hence, every record in the target table is updated at most once.
|
43

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.

Improve this answer

1 Comment

This version is arguably cleaner, but it is not trigger friendly because there is no way I am aware of to avoid triggering update triggers for unchanged rows using this syntax. (I am assuming that the triggers are needed for the changed rows.)
24

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.

Improve this answer

2 Comments

Short n Perfect
SQL Error: ORA-01779: cannot modify a column which maps to a non key-preserved table
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
Improve this answer

2 Comments

SQL Error: ORA-01779: cannot modify a column which maps to a non key-preserved table
Getting error :: => SQL Error: ORA-01779: cannot modify a column which maps to a non key-preserved table 01779. 00000 - "cannot modify a column which maps to a non key-preserved table" *Cause: An attempt was made to insert or update columns of a join view which map to a non-key-preserved table. *Action: Modify the underlying base tables directly.
9

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.

Improve this answer

1 Comment

This doesn't work for me. It updates the entire table.
6

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
Improve this answer

2 Comments

Can set multiple properties by adding a comma at the end of that. I needed to do t1.First_Name = t2.FirstName, t1.Last_Name = t2.LastName on a table after matching it on the "UserName" column (t1.UserName = t2.UserName) to retrieve their name from a table called UserInfo (select * from UserInfo) t2). The database was such where it was using UserName as a primary key to UserInfo everywhere, instead of placing FirstName and LastName in the table, directly. This fixed that!
This answer adds nothing to the answer already provided by Quassnoi five years before yours.
4

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;
Improve this answer

2 Comments

@JimGarrison Please re-edit this answer so I can remove my downvote.... I was trying to use this syntax and it wasn't updating my table. I found out why - my SET was doing a REPLACE and I was trying to blank a particular string in the column - turns out Oracle treats '' as null, and this field could not be nulled. I thought the syntax was merely updating a temp table instead of the real one, but I was wrong.
Getting error :: => SQL Error: ORA-01779: cannot modify a column which maps to a non key-preserved table
2

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' ;
Improve this answer

1 Comment

why u want to fire two separate queries on table2
1 
UPDATE table1 t1 SET t1.value = (select t2.CODE from table2 t2 where t1.value = t2.DESC) WHERE t1.UPDATETYPE='blah';
Improve this answer

Comments

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

Improve this answer

3 Comments

Hi Dan. You are posting to a pretty old question that already has very good answers. Can you explain when you question is preferable over the other solutions?
Of course, I've seen an answer where b = a were written by pointing the table name (table1.B = table2.A) but there's no need to point the table.
You are actually updating fields from the view, which get mapped to the table. If the inner view were aliased h, then the "self-documenting" version would be "set h.b = h.a".
1

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; /
Improve this answer

1 Comment

This could do it, but it's about the slowest way possible.
0 
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`
Improve this answer

Comments

0

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.

Improve this answer

Comments

0

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
Improve this answer

Comments

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

Comments

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.