ChatGPT解决这个技术问题 Extra ChatGPT

Update statement with inner join on Oracle

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';
When I tried to setup table2 in Oracle to test my answer I found that Oracle rejected DESC as a column name.
Sorry I just abbreviated the original column name to desc its obviously not that in the db

B
Barbaros Özhan

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


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

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;

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

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.


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

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.


Short n Perfect
M
Morten Anderson
 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

A
Alexis Dufrenoy

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.


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

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

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.
J
Jim Garrison

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;

@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.
J
Janek Bogucki

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

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

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


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".
u
user5685973
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`

E
Edu Castrillon

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

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

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.


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