By user169743

2010-03-15 11:40:21 8 Comments

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


@Quassnoi 2010-03-15 11:58:30

Use this:

INTO    table1 trg
        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)
    SET trg.value = code;

@Michael-O 2013-05-06 12:12:22

Works perfectly but Oracle required me to say merge into table 1 t and so forth.

@Marc 2014-07-21 19:02:13

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

@Henrique 2016-04-25 12:25:49

Just a point here, if your key (trg.rowid or src.rid) has one duplicated item this clause throw an error:

@vapcguy 2016-07-22 20:32:48

@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 2018-01-15 03:52:06

@Quassnoi You answered "Oracle does not support joins in the UPDATE statements" but Tony Andrews 2nd answer above shows using join within update statement and couple of guys tested it's working! What's the catch here?

@Quassnoi 2018-01-15 13:03:07

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

@Aswin Alagappan 2018-05-17 16:16:36

@Sonny: Oracle older versions requires WHEN NOT MATCHED THEN clause to be present

@mathguy 2018-11-28 13:00:35

Oracle does support joins in UPDATE statement, you just have to use the correct syntax. I edited your Answer to remove the false statement to the contrary.

@test 2015-11-27 15:58:43

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

@Dan Anderson 2017-05-30 09:47:58

SET B = A;

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

@Noel Widmer 2017-05-30 09:52:12

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?

@Dan Anderson 2017-05-30 11:31:01

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.

@duvo 2017-04-04 22:36:54

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.

@Roland 2015-05-07 14:12:15

Merge with where clause worked for me:

merge into table1
using table2
on ( =
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.

@Hemant 2013-01-24 05:18:48

This following syntax works for me.

(SELECT A.utl_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;

@vapcguy 2016-07-22 21:03:58

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

@Tony Andrews 2010-03-15 11:53:02

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'
            FROM table2 
            WHERE table1.value = table2.DESC);

Or you might be able to do this:

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

(It depends if the inline view is considered updateable by Oracle).

@jonny 2011-02-02 13:47:31

I'am writing similar query portable between dbms: oracle considers inline view is updatable, but mysql not

@Gustavo Rubio 2011-06-06 02:25:37

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

@Pisu 2011-12-12 16:51:25

Tried with success the first syntax, thanks

@Daniel Reis 2012-01-19 10:18:14

The second example has the benefit of allowing you to test the SQL before actually performing the update.

@nemo 2012-05-02 00:03:20

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.

@Alejandro Bastidas 2013-03-11 15:37:17

Worked for me but with a different set statement. set value = code

@Michael-O 2013-05-06 12:11:45

Does not work with Oracle 11.2g but Quassnoi's solution works.

@elrado 2014-02-18 05:22:57

Second worked for me :). Oracle is one strong but weird animal :/

@Vadzim 2015-02-12 12:17:51

Explanation on key-preserved requirement for updatable joins:…

@Swapnil 2015-09-16 06:30:24

Got this - "ORA-01779: cannot modify a column which maps to a non key-preserved table", and then… helped.

@Dzmitry Lahoda 2016-06-08 08:28:48

I had similar case, but wanted to update table1.value = 'Single Value'. In this case EXISTS becomes any of instead for each, hence updates things which should not be updated. I used second instead. Both queries work in 11.2.

@Dave 2017-11-23 20:38:50

Can't count the number of times I've got back to this answer... The last times I google it waiting to see this reply... Thx for this answer, @Tony Andrews

@Skipper 2018-03-01 08:45:51

@elrado Oracle is one handicapped animal if You want to do that kind of comparisons :)

@user2171669 2018-07-20 21:15:36

This is basically a simple trick using ALIASES (that's a powerful tool). You can put as many joins in inner select as you want. And the exists clause (which is the same as inner select) needed to ensure assigned value presence (otherwise will be null I suppose).

@Marshall 2018-12-26 16:07:39

I'm getting this error, "ORA-01779: cannot modify a column which maps to a non key-preserved table.

@Alexis Dufrenoy 2016-01-14 12:37:44

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.

@Natassia Tavares 2017-11-22 12:14:36

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

@user5685973 2015-12-16 09:53:24

  ON ip.ip_visit_id=v.visit_id
  AND v.pat_id     =3702
  ); `enter code here`

@user5299305 2015-09-04 04:40:53

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

@vapcguy 2016-07-20 21:56:31

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!

@Forage 2017-11-14 14:18:31

This answer adds nothing to the answer already provided by Quassnoi five years before yours.

@afnhsn 2014-06-05 07:59:54

UPDATE table1 t1
SET t1.value = 
    (select t2.CODE from table2 t2 
     where t1.value = t2.DESC) 

@Morten Anderson 2010-03-15 11:58:12

 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

@Janek Bogucki 2010-03-15 12:04:07

Using description instead of desc for table2,

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

@Jitendra Vispute 2015-09-14 08:19:29

why u want to fire two separate queries on table2

Related Questions

Sponsored Content

27 Answered Questions

[SOLVED] What is the difference between "INNER JOIN" and "OUTER JOIN"?

8 Answered Questions

[SOLVED] Difference between JOIN and INNER JOIN

32 Answered Questions

[SOLVED] How do I UPDATE from a SELECT in SQL Server?

16 Answered Questions

[SOLVED] How to Delete using INNER JOIN with SQL Server?

7 Answered Questions

4 Answered Questions

[SOLVED] Oracle insert if not exists statement

  • 2012-05-30 21:51:41
  • user1427096
  • 87546 View
  • 26 Score
  • 4 Answer
  • Tags:   oracle

3 Answered Questions

2 Answered Questions

Update sql table by joining 4 tables

10 Answered Questions


4 Answered Questions

[SOLVED] SQL Inner join on select statements

  • 2009-12-21 14:59:26
  • user235693
  • 61053 View
  • 16 Score
  • 4 Answer
  • Tags:   sql oracle ora-00933

Sponsored Content