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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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  gt  table2 start date    You need the WHERE clause because columns referenced in the ON clause cannot be updated

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

UPDATE table1 t1 SET t1 value         select t2 CODE from table2 t2       where t1 value   t2 DESC   WHERE t1 UPDATETYPE  blah

User · Answer

update table1  a     set a col1  Y    where exists select 1                  from table2 b                where a col1 b col1                   and a col2 b col2

User · Answer

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

User · Answer

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

User · Answer

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

[sql] Update statement with inner join on Oracle

Examples related to sql

Examples related to oracle

Examples related to inner-join

Examples related to ora-00933