Skip to Main Content
  • Questions

  • conversion of MSACCESS query to Oracle SQL

Breadcrumb

Question and Answer

Connor McDonald

Thanks for the question, Joseph.

Asked: September 29, 2017 - 7:58 pm UTC

Last updated: October 05, 2017 - 4:03 pm UTC

Version: 11g

Viewed 1000+ times

You Asked

The Goal is to convert a successful MS_ACCESS Query to Oracle SQL.



Access Query
------------------
UPDATE target_table T
INNER JOIN source_table S
ON T.linkcolumn = S.linkColumn
SET
T.field1 = S.field1,
T.field2 = S.field2,
T.field3 = S.field3;

This ACCESS query will update all records in the target that match the LinkColumn Value in Source record, even if there are duplicate linkColumn values in the Source table.

Row..Link..Target........................Row Link...Source
1.......A......Field1, Field2, Field3 = 1......A........Field1, Field2, Field3
2.......A......Field1, Field2, Field3 = 2......A........Field1, Field2, Field3 3.......A......Field1, Field2, Field3 .......................................................... (MS access uses the last valid row)
(3 target rows are updated from Source Row 2 (even if a duplicate record exists)


Source linkColumn cannot be a Primary key
Target linkColumn cannot be Primary key

----------------------------------------------------

Oracle SQL
This code produces an "unable to get stable set of rows" error because
the target table has instances (1 or more) records attempting to match (1 or more) records in the source table

MERGE INTO target_table T
USING
(
SELECT * FROM source_table
) S
ON
(
T.manyRecsLinkColumn = S.oneRecLinkColumn
)
WHEN MATCHED THEN
UPDATE SET
T.columnToUpdate1 = S.Value1,
T.columnToUpdate2 = S.Value2,
T.columnToUpdate3 = S.Value3
-------------------------------------------------------

Questions:

1. Is there an Oracle SQL statement which will Update multiple records when there is
many-to-many relationship between the source (many) and target (many)

2. Is there some equivalent in Oracle to FindFirst, Update, FindNext, Update, FindNext and Update until noMoreMatches.

3. Is there an refinement of the MERGE USING statement (above) that uses a FINDNEXT (as suggested in #2)

4. If there is a no Oracle Statement that applies to this Update of 1-to-Many relationship, would a Stored Procedure be the next best approach?

Source.MOVEFIRST
DO WHILE NOT Source.EOF
.......Initiate Vars
StartOrRepeat:
.......FIND(NEXT) matching record
............If noMatch goto Next1
.......Update fields in target
.......Goto StartorRepeat
Next1:
.......MOVENEXT in SOURCE until End of Table
LOOP

and Connor said...

Will this suffice ?

UPDATE target_table T 
SET 
T.field1 = S.field1, 
T.field2 = S.field2,
T.field3 = S.field3
where T.linkcolumn in 
( select S.linkColumn
 from source_table S 
);


Rating

  (3 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Convert MS Access Query to Oracle SQL

Joseph Giallombardo, October 02, 2017 - 4:29 pm UTC

The Goal is to convert a successful MS_ACCESS Query to Oracle SQL.



Access Query
------------------
UPDATE target_table T
INNER JOIN source_table S
ON T.linkcolumn = S.linkColumn
SET
T.field1 = S.field1,
T.field2 = S.field2,
T.field3 = S.field3;

and we said...

Will this suffice ?

UPDATE target_table T
SET
T.field1 = S.field1,
T.field2 = S.field2,
T.field3 = S.field3
where T.linkcolumn in
( select S.linkColumn
from source_table S
);


Sorry, this provides error S.field3 INVALID...any other thoughts?

JG


Code provided is not sufficient.

Joseph Giallombardo, October 04, 2017 - 7:09 pm UTC

This script did not suffice because their are duplicate records in each table.
UPDATE target_table T
SET
T.field1 = S.field1,
T.field2 = S.field2,
T.field3 = S.field3
where T.linkcolumn in
( select S.linkColumn
from source_table S
);

This script did not suffice either for the same reason:
MERGE INTO DOBS.CP_ITEMIZED I
USING (
SELECT A.CREATED_BY, A.APPLICABLE_MANUFACTURER, A.SOURCE_FILE, A.REPORT
FROM DOBS.CP_INT_USERS_APPLICABLE_MFR A
GROUP BY A.CREATED_BY, A.APPLICABLE_MANUFACTURER, A.SOURCE_FILE, A.REPORT
) T
ON
(
I.SOURCE_FILE_NAME = T.SOURCE_FILE
AND I.SOURCE_FILE_NAME IS NOT NULL
)
WHEN MATCHED THEN
UPDATE SET
I.CREATED_BY = T.CREATED_BY,
I.APPL_MFG_OR_GPO_MAKE_PMT_NAME = T.APPLICABLE_MANUFACTURER,
I.REPORT = T.REPORT

So perhaps I need a stored procedure, to supply variables and update the target based on a FIND...WHEN MATCHED UPDATE.

I will give it a rest a submit new question...unless there is another idea you might have. thanks.

Connor McDonald
October 05, 2017 - 4:03 pm UTC

Sorry, I pressed Submit too soon.

UPDATE target_table T 
SET 
T.field1 = S.field1, 
T.field2 = S.field2, 
T.field3 = S.field3 
where T.linkcolumn in 
( select S.linkColumn 
from source_table S 
); 
<code>

should be

<code>
UPDATE target_table T 
SET 
(T.field1 
,T.field2 
,T.field3
)
= ( select s.field1, s.field2, s.field3 from source_table s 
    where T.linkcolumn = S.linkColumn  )
where T.linkcolumn in 
( select S.linkColumn 
from source_table S 
); 
<code>

and if you get duplicates and want to pick an arbitrary one, you can do:

<code>
UPDATE target_table T 
SET 
(T.field1 
,T.field2 
,T.field3
)
= ( select s.field1, s.field2, s.field3 from source_table s 
    where T.linkcolumn = S.linkColumn and rownum = 1 )
where T.linkcolumn in 
( select S.linkColumn 
from source_table S 
);



Variant

Racer I., October 05, 2017 - 10:30 am UTC

Hi,

This might work, but the requirement (as I understand it) is weird.

drop table target;
drop table source;
create table target (lc NUMBER, F1 NUMBER, F2 NUMBER, F3 NUMBER);
create table source (lc NUMBER, F1 NUMBER, F2 NUMBER, F3 NUMBER);
insert into target Values (1, 2, 3, 4);
insert into target Values (1, 5, 6, 7);
insert into target Values (2, 8, 9, 0);
insert into source Values (1, 3, 3, 1);
insert into source Values (1, 5, 5, 1);
insert into source Values (1, 7, 7, 1);
insert into source Values (2, 8, 8, 2);
insert into source Values (2, 9, 9, 2);
select * from target;
select * from source;
UPDATE target t set (f1, f2, f3) = (Select f1, f2, f3 FROM source s where t.lc = s.lc);
UPDATE target t set (f1, f2, f3) = (Select f1, f2, f3 FROM source s where s.rowid = (select MAX(ROWID) KEEP (DENSE_RANK FIRST ORDER BY ROWID) FROM source s2 WHERE t.lc = s2.lc));
select * from target;
rollback;

Table dropped.
Table dropped.
Table created.
Table created.
1 row created.
1 row created.
1 row created.
1 row created.
1 row created.
1 row created.
1 row created.
1 row created.

        LC         F1         F2         F3
---------- ---------- ---------- ----------
         1          2          3          4
         1          5          6          7
         2          8          9          0

3 rows selected.

        LC         F1         F2         F3
---------- ---------- ---------- ----------
         1          3          3          1
         1          5          5          1
         1          7          7          1
         2          8          8          2
         2          9          9          2

5 rows selected.
UPDATE target t set (f1, f2, f3) = (Select f1, f2, f3 FROM source s where t.lc = s.lc)
Error at line 15
ORA-01427: ...

3 rows updated.

        LC         F1         F2         F3
---------- ---------- ---------- ----------
         1          3          3          1
         1          3          3          1
         2          8          8          2

3 rows selected.
Rollback complete.

more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's latest video and Chris's latest video from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.

More to Explore

SQL

The Oracle documentation contains a complete SQL reference.