Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people, just like you, are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
Solved

Oracle Update Query Help

Posted on 2011-09-08
8
282 Views
Last Modified: 2012-05-12
Hello,

I am mostly a SQL Server user, and I have need to create an update query in Oracle.  Below is my query from SQL Server, how would that translate to Oracle SQL?

Thanks,
-Torrwin
UPDATE Master_ID_Table
SET Table2_ID = B.ID2
FROM Master_ID_Table A INNER JOIN (SELECT Table1.ID AS ID1, Table2.ID AS ID2 FROM Table1 INNER JOIN Table2 ON Table1.Field = Table2.Field) B ON A.Table1_ID = B.ID1
WHERE A.Table2_ID IS NULL;

Open in new window

0
Comment
Question by:Torrwin
  • 4
  • 3
8 Comments
 
LVL 74

Expert Comment

by:sdstuber
ID: 36504929
how about this?
ee.txt
0
 
LVL 74

Accepted Solution

by:
sdstuber earned 500 total points
ID: 36504951
or this...
ee.txt
0
 
LVL 13

Author Comment

by:Torrwin
ID: 36505281
Thanks, those both make sense.  I kicked off the second one and it's been going for some time now.  From an efficiency standpoint, is the first one better since it's part of the join and not the where clause?
0
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 74

Expert Comment

by:sdstuber
ID: 36505560
shouldn't really matter efficiencywise because they are inner joins so it should be a pushable predicate.
0
 
LVL 13

Author Comment

by:Torrwin
ID: 36506135
Hmm, neither of these seem to be working.  The first give me a "ORA-00904:'A'.'table1_id': invalid identifier" error on the extra join, and the second gives me a "ORA-01427: single-row subquery returns more than one row" error.  I have re-verified that all the source tables contain unique values.

Thoughts?
0
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
ID: 36509997
My ansi join syntax is pretty weak and I likely have the syntax wrong but try an update like this:

UPDATE (
select b.id2, table2_id
FROM Master_ID_Table A INNER JOIN (SELECT Table1.ID AS ID1, Table2.ID AS ID2 FROM Table1 INNER JOIN Table2 ON Table1.Field = Table2.Field) B ON A.Table1_ID = B.ID1
WHERE A.Table2_ID IS NULL)
SET Table2_ID = ID2;
0
 
LVL 74

Assisted Solution

by:sdstuber
sdstuber earned 500 total points
ID: 36510012
if your're getting ORA-01427 then you're not going to be able to update your data.


select * from (
select a.table1_id, (SELECT count(*)
              FROM (SELECT table1.id AS id1, table2.id AS id2
                      FROM table1 INNER JOIN table2 ON table1.field = table2.field) b
             WHERE a.table1_id = b.id1) cnt
from master_id_table a
) where cnt > 1

after looking at those id's and the corresponding id2's check if it's the same value being returned more than once, if so, just add distinct


UPDATE master_id_table a
   SET a.table2_id      =
           (SELECT DISTINCT id2
              FROM (SELECT table1.id AS id1, table2.id AS id2
                      FROM table1 INNER JOIN table2 ON table1.field = table2.field) b
             WHERE a.table1_id = b.id1)
 WHERE a.table2_id IS NULL;
0
 
LVL 13

Author Closing Comment

by:Torrwin
ID: 36524456
Thanks!  Apparently even though my separate tables consisted of distinct values, when combined together they had a couple of duplicate ID's.
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Configuring and using Oracle Database Gateway for ODBC Introduction First, a brief summary of what a Database Gateway is.  A Gateway is a set of driver agents and configurations that allow an Oracle database to communicate with other platforms…
Checking the Alert Log in AWS RDS Oracle can be a pain through their user interface.  I made a script to download the Alert Log, look for errors, and email me the trace files.  In this article I'll describe what I did and share my script.
This video shows information on the Oracle Data Dictionary, starting with the Oracle documentation, explaining the different types of Data Dictionary views available by group and permissions as well as giving examples on how to retrieve data from th…
This video explains what a user managed backup is and shows how to take one, providing a couple of simple example scripts.

809 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question