[Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 294
  • Last Modified:

Oracle Update Query Help

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
Torrwin
Asked:
Torrwin
  • 4
  • 3
2 Solutions
 
sdstuberCommented:
how about this?
ee.txt
0
 
sdstuberCommented:
or this...
ee.txt
0
 
TorrwinAuthor Commented:
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
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
sdstuberCommented:
shouldn't really matter efficiencywise because they are inner joins so it should be a pushable predicate.
0
 
TorrwinAuthor Commented:
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
 
slightwv (䄆 Netminder) Commented:
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
 
sdstuberCommented:
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
 
TorrwinAuthor Commented:
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

Veeam Disaster Recovery in Microsoft Azure

Veeam PN for Microsoft Azure is a FREE solution designed to simplify and automate the setup of a DR site in Microsoft Azure using lightweight software-defined networking. It reduces the complexity of VPN deployments and is designed for businesses of ALL sizes.

  • 4
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now