SQL insert data with inner join using oracle

Question

New to SQL I have two tables table_A and table_B.

I want to add data into a specific column of table_A depending on a inner join on table_B. I am using Oracle, following this method my SQL looks like this :

I first tried that :

INSERT INTO table_A (target_column)
SELECT table_B.wanted_data 
FROM table_B INNER JOIN table_A ON table_B.someColumnB = table_A.someColumnA

Here the issue is that it would insert the data in new lines of my table_A and not in the existing lines.

So I tried that from the stackoverflow thread :

UPDATE(SELECT table_A.target_column, table_B.wanted_data
  FROM table_A
  INNER JOIN table_B
  ON table_A.someColumnA = table_B.someColumnB
)
SET table_A.target_table = table_B.wanted_data

But it is not working either "SQL command not properly ended"

EDIT : target_column and wanted_data have the same name in my data set, not sure if it changes anything.

Did you actually miss to check that the SET statement should be `table_A.UsineId` instead of `table_A_UsineId` in your main query? — Kaushik Nayak, Feb 21 '18 at 16:26
Have you gone through that thread completely and tried all alternatives?For eg: You may also use `MERGE INTO` . I think you have probably made a mistake while copying and modifying your query. — Kaushik Nayak, Feb 21 '18 at 16:32

Vallack · Accepted Answer · 2018-02-21T16:45:25.313

1

SQL Sever:

UPDATE a 
SET    a.target_column = b.wanted_data 
FROM   table_A a 
JOIN   table_B b 
ON     b.someColumnB = a.someColumnA

Oracle:

UPDATE
(
    SELECT     b.wanted_data   AS wanted_data
              ,a.target_column AS old_data
    FROM       table_A a
    INNER JOIN table_B b
    ON         b.someColumnB = a.someColumnA
)   c
SET c.old_data = c.wanted_data

edited Feb 21 '18 at 16:45

answered Feb 21 '18 at 16:18

Vallack

49
4

It seems that you can't update & join like that using Oracle – Logan Wlv Feb 21 '18 at 16:19
@LoganWlv Ah sorry missed that it was Oracle, I have updated my answer. – Vallack Feb 21 '18 at 16:26
it seems I have an issue "column ambiguously defined", I think it's due to the same name from my target_column and wanted_data , check my last edit – Logan Wlv Feb 21 '18 at 16:41
@LoganWlv ah okay give the selections aliases so something like SELECT b.wanted_data AS wanted_data, a.target_column AS old_data....... SET c.old_data = c.wanted_data, I'll edit my answer again. – Vallack Feb 21 '18 at 16:43
@LoganWlv you're missing a name for the selection. "SQL command not properly ended" note the 'c' in my code at the end of my SELECT. – Vallack Feb 21 '18 at 16:51
Ok i will try it – Logan Wlv Feb 21 '18 at 16:51
Ok I have an other issue "cannot modify a column which maps to a non key-preserved table" but the request is working so I will validate your answer and searching to resolve this other issue, thanks ! :) – Logan Wlv Feb 21 '18 at 16:56

William Robertson · Answer 2 · 2018-02-22T09:22:15.313

Regarding this approach, which you said fails with "SQL command not properly ended":

(This is the failing code:)

update
   ( select table_a.target_column
          , table_b.wanted_data
     from   table_a
            join table_b
                 on  table_b.somecolumnb = table_a.somecolumna
    )
set table_a.usineid = table_b.usineid;

I can't see why you would get that error, but the last line is incorrect because labels table_a and table_b only exist inside the inline view, which is anonymous. (Also there is no UsineId but I am assuming that is a copy-paste issue in your example.) It needs to be either:

update
   ( select table_a.target_column
          , table_b.wanted_data
     from   table_a
            join table_b
                 on  table_b.somecolumnb = table_a.somecolumna
    )  -- you are updating an anonymous inline view
set target_column = wanted_data;

or

update
   ( select table_a.target_column
          , table_b.wanted_data
     from   table_a
            join table_b
                 on  table_b.somecolumnb = table_a.somecolumna
    ) v  -- you are updating an inline view named V
set v.target_column = v.wanted_data;

Then, there needs to be a unique index or constraint on the parent key, table_b.somecolumnb, otherwise Oracle will refuse to attempt any update with

ORA-01779: cannot modify a column which maps to a non key-preserved table

Alternatively, you can use merge:

merge into table_a a
using table_b b
      on (b.somecolumnb = a.somecolumna)
when matched then update set a.target_column = b.wanted_data;

score 0 · Answer 3 · answered Feb 21 '18 at 16:30

Give this a try:

update  table_A A
set     A.target_column = (
        select table_B_data 
        from   table_B 
        where  shared_key = A.shared_key
        );

This will only work if there is one row with table_B_date in table_B. If there are multiple rows, you need to specify the key

SQL insert data with inner join using oracle

3 Answers3