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

14 comments

@Edu Castrillon 2019-02-19 08:58:50

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

@APC 2019-03-19 12:37:45

This could do it, but it's about the slowest way possible.

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

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;

@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: ora-30926.ora-code.com

@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

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.

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

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

Merge with where clause worked for me:

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.

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

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;

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

@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: asktom.oracle.com/pls/asktom/…

@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 stackoverflow.com/questions/9335536/… 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.

@khalidmehmoodawan 2019-04-11 01:57:04

That was quick. !!! Cheers.

@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

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`

@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) 
WHERE t1.UPDATETYPE='blah';

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

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

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

why u want to fire two separate queries on table2

Related Questions

Sponsored Content

28 Answered Questions

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

7 Answered Questions

33 Answered Questions

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

3 Answered Questions

8 Answered Questions

[SOLVED] Difference between JOIN and INNER JOIN

15 Answered Questions

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

4 Answered Questions

[SOLVED] Oracle insert if not exists statement

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

2 Answered Questions

Update sql table by joining 4 tables

10 Answered Questions

[SOLVED] INNER JOIN ON vs WHERE clause

4 Answered Questions

[SOLVED] SQL Inner join on select statements

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

Sponsored Content