2

I have the following MySQL query:

UPDATE spot ST 
INNER JOIN tag_mapping_spot c ON c.spot_id = ST.spot_id 
INNER JOIN def_table b ON b.tag_id = c.tag_id 
SET ST.spot_name = b.tag_ja

Recently I move to oracle and want to convert this query to oracle version. I searched and tried

UPDATE ( SELECT spot.spot_name as f1, def_table.tag_ja as f2 
        FROM spot 
        INNER JOIN tag_mapping_spot 
        ON tag_mapping_spot.spot_id = spot.spot_id
        INNER JOIN def_table
        ON def_table.tag_id = tag_mapping_spot.tag_id
        ) j
SET j.f2 = j.f1

and 

MERGE into spot
USING tag_mapping_spot
ON (tag_mapping_spot.spot_id = spot.spot_id)
USING def_table
ON (def_table.tag_id = tag_mapping_spot.tag_id)
WHEN MATCHED THEN UPDATE SET spot.spot_name = def_table.tag_jae

But none of them work. How should I deal with this? Thanks in advance

Hoàng Việt
  • 174
  • 1
  • 14

3 Answers3

2

You can use an inline view within a MERGE statement :

 MERGE INTO spot s
 USING ( SELECT distinct st.spot_name, c.spot_id , b.tag_ja
           FROM spot st 
           JOIN tag_mapping_spot c ON c.spot_id = st.spot_id 
           JOIN def_table b ON b.tag_id = c.tag_id ) tt
    ON (s.spot_id = tt.spot_id)
  WHEN MATCHED THEN
UPDATE SET s.spot_name = tt.tag_ja;
Barbaros Özhan
  • 59,113
  • 10
  • 31
  • 55
  • I got the following error: "ORA-01779: cannot modify a column which maps to a non key-preserved table." – Hoàng Việt Oct 31 '19 at 06:53
  • 1
    Vote up! Just a notice, this will not work in case you have a double id entry in some table like for example: https://dbfiddle.uk/?rdbms=oracle_11.2&fiddle=e109909b5d9f06f03bee9e367a77b304 – VBoka Oct 31 '19 at 07:42
  • 1
    Thank you, sorry not to mention but my ```spot_id``` column is unique and auto-increment so there is no double id entry in my table – Hoàng Việt Oct 31 '19 at 07:49
  • UPDATE: I tried your query with huge amount of data (10000) and It's return this error "ORA-30926: unable to get a stable set of rows in the source tables" . What 's that mean ?? – Hoàng Việt Nov 08 '19 at 04:27
  • it's not a flaw for the statement, but related to the data structure within the table which comes from the inline query after `USING` clause. I added an extra `distinct` against the issue. Consider [this](https://stackoverflow.com/a/2337465/5841306). – Barbaros Özhan Nov 08 '19 at 05:17
1

Here is what you can use for UPDATE :

UPDATE spot s
SET s.spot_name = (SELECT def_table.tag_ja as f2
                      FROM def_table
                      INNER JOIN tag_mapping_spot
                      ON tag_mapping_spot.tag_id = def_table.tag_id
                      WHERE tag_mapping_spot.SPOT_ID = s.SPOT_ID)
WHERE s.spot_id in (select spot_id from tag_mapping_spot );

Here is the DEMO

And here is a nice script to show you how to use MERGE: SCRIPT

And in case you have double entry in all tables then check this DEMO

UPDATE spot s
SET s.spot_name = (SELECT max(def_table.tag_ja) as f2
                      FROM def_table
                      INNER JOIN tag_mapping_spot
                      ON tag_mapping_spot.tag_id = def_table.tag_id
                      WHERE tag_mapping_spot.SPOT_ID = s.SPOT_ID)
WHERE s.spot_id in (select spot_id from tag_mapping_spot );
VBoka
  • 8,995
  • 3
  • 16
  • 24
  • Your DEMO is incorrect. I just fixed the sample data. Can you take a look ```https://dbfiddle.uk/?rdbms=oracle_11.2&fiddle=5adccad1169c125715809521f2498b44``` – Hoàng Việt Oct 31 '19 at 07:32
  • 1
    I have updated my answer and corrected the code. It will now work. – VBoka Oct 31 '19 at 07:51
0

You can write your query as follow:

update spot set spot_name = 
 (SELECT def_table.tag_ja as f2 
        FROM spot 
        INNER JOIN tag_mapping_spot 
        ON tag_mapping_spot.spot_id = spot.spot_id
        INNER JOIN def_table
        ON def_table.tag_id = tag_mapping_spot.tag_id)

Hope this helps:)

Hoàng Việt
  • 174
  • 1
  • 14
Sheetal Nanda
  • 18
  • 3