Solved: Update statement using inner join

Create Account

Oracle Database

Questions

Followers

Top Experts

servantis

Update statement using inner join

I have two tables ALPHA and BETA. I need to update the BETAID to the ALPHAID. The BETAID is currently set to the ALPHADESC so I am joining on this column.

The following update statement works fine in SQL Server:

UPDATE BETA
SET BETAID= A.ALPHAID
FROM TABLE BETA
INNER JOIN ALPHA A
ON BETAID= A.ALPHADESC

So using the lookup table Alpha:-

ALPHAID ALPHADESC
1 First
2 Second
3 Third

This changes the Beta table from:-

BETAID
First
Second
Third

To:-

BETAID
1
2
3

So, the query is simply using a lookup table to replace a description with its corresponding ID, one row at a time. I now need to replicate this query in Oracle. Using the following:-

UPDATE BETA
SET BETAID = (SELECT A.ALPHAID FROM ALPHA A, BETA B
WHERE B.BETAID = A.ALPHADESC);

Results in 'single-row subquery returns more than one row', as expected. How would I rewrite this query for oracle?

Zero AI Policy

We believe in human intelligence. Our moderation policy strictly prohibits the use of LLM content in our Q&A threads.

Harisha M G🇮🇳

Hi servantis, try this:

UPDATE BETA
SET BETAID = (SELECT MAX(A.ALPHAID) FROM ALPHA A, BETA B
WHERE B.BETAID = A.ALPHADESC);

---
Harish

servantis

ASKER

Harish, MAX(A.ALPHAID) sets, all the BETAID's to the highest value in the lookup table, so if I have a table with 1/3 'First', 1/3 'Second' and 1/3 'Third, it sets all the BETAIDs to '3', which is incorrect. I've also tried group by, having count and distinct and none of these update the rows one at a time like the inner join syntax does.

Harisha M G🇮🇳

Why don't you use the same inner join in Oracle ?

EARN REWARDS FOR ASKING, ANSWERING, AND MORE.

Earn free swag for participating on the platform.

servantis

ASKER

It doesn't seem to work. I'm on Oracle 9.2.0.1.0. Should it work?

Is the 'inner join syntax' supported in oracle. If so does it differ from the SQL Server standard?

Harisha M G🇮🇳

I am not an expert at joins :)

But I think you need to remove "TABLE" from your SQL query..

UPDATE BETA SET BETAID= A.ALPHAID
FROM BETA INNER JOIN ALPHA A
ON BETAID= A.ALPHADESC

servantis

ASKER

No luck I'm afraid. Oracle doesn't seem to like the 'FROM' clause. Any other suggestions?

Get a FREE t-shirt when you ask your first question.

We believe in human intelligence. Our moderation policy strictly prohibits the use of LLM content in our Q&A threads.

Harisha M G🇮🇳

What error did it give ?

Harisha M G🇮🇳

http://www.oracle.com/oramag/oracle/01-nov/o61sql.html

jrb1🇺🇸

In oracle, this works better:

UPDATE BETA
SET BETAID= (select ALPHAID from alpha where betaid = alphadescr)
where exists (select ALPHAID from alpha where betaid = alphadescr)

EARN REWARDS FOR ASKING, ANSWERING, AND MORE.

Earn free swag for participating on the platform.

jazzyline

If there are no duplicates then this should work:
UPDATE beta b
SET b.betaid = (SELECT a.alphaid
FROM alpha a
WHERE a.alphadesc = b.betaid)

If there dupes then this will work:
UPDATE beta b
set b.betaid = (SELECT a.alphaid
FROM (SELECT a.alphaid,a.alphadesc
FROM alpha a
GROUP BY a.alphaid,a.alphadesc) a
WHERE a.alphadesc = b.betaid)

awking00🇺🇸

update beta
set beta.betaid = (select alpha.alphaid from alpha
where beta.betaid = alpha.alphadesc);

servantis

ASKER

What if I want update a table based on joins to several other tables? In sql server I can achieve this through several inner joins.

Get a FREE t-shirt when you ask your first question.

We believe in human intelligence. Our moderation policy strictly prohibits the use of LLM content in our Q&A threads.

jazzyline

servantis,
Sure, you can achieve the same thing in Oracle as well, in which case you'll just need to modify the above mentioned query to include the join conditions.

ASKER CERTIFIED SOLUTION

jrb1🇺🇸

membership

Signing up is free and takes 30 seconds. No credit card required.

Create Account

RCorfman

yes, you need the exists to ensure only rows with matching values are updated and the max to ensure that you only get one row on the updating subquery, also, the subquery has to be a 'collated' subquery so that it retrieves child records that match the parent. The "and exists ...." can be left out if ou know there is a matching record in alpha for every record in beta.

UPDATE BETA
SET BETAID = (SELECT max(A.ALPHAID) FROM ALPHA A, BETA B
WHERE B.BETAID = A.ALPHADESC)
and exists (select 'x' from alpha a, beta b where b.betaid=a.alphadesc);

Oracle Database

Questions

Followers

Top Experts

Oracle is an object-relational database management system. It supports a large number of languages and application development frameworks. Its primary languages are SQL, PL/SQL and Java, but it also includes support for C and C++. Oracle also has its own enterprise modules and application server software.