Avatar of cbridgman
cbridgman
Flag for United States of America asked on

Oracle Script to Update One Table's Field Based on Another Table

I'm sure this is a simple query but here goes ... I have two related tables A and B. One record in table A might have many related records in table B. If it does, I want to update one column in all of table B's related records with a specific value based on a value in Table A.

So, for example, if TABLEA.COLUMN3 = 123 then I want to update TABLEB.COLUMN5 values to 'ABC' in all related TABLEB records. Another example  --> if TABLEA.COLUMN1 = 456, then I want to update TABLEB.COLUMN5 values to 'DEF' in all related TABLEB records.

Something like (I know this isn't right):

update TABLEB
from TABLEA
set TABLEB.COLUMN5 = 'ABC'
where TABLEA.COLUMN3 = 123 and TABLEA.COLUMN1 = TABLEB.COLUMN2
Oracle Database

Avatar of undefined
Last Comment
cbridgman

8/22/2022 - Mon
slightwv (䄆 Netminder)

When I see an update involving two tables I immediately jump to using MERGE to do it.

I posted MERGE syntax in your previous question that seems to be pretty similar to this one:
https://www.experts-exchange.com/questions/28646923/Oracle-Cannot-Update-To-Null.html?anchorAnswerId=40697426#a40697426

Even if you don't use MERGE, you should be able to use the selected answer from there:
update with a sub-select.

If you can't get it working, please provide some sample data and expected results.
SOLUTION
Sean Stuber

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
GET A PERSONALIZED SOLUTION
Ask your own question & get feedback from real experts
Find out why thousands trust the EE community with their toughest problems.
cbridgman

ASKER
Okay, a real life example is attached. As noted, each update can be a single statement. I don't need to make just a single update to handle it all.
cbridgman

ASKER
I posted the following earlier with a different question by mistake.

**************************

I'd rather not use the merge because I have to apply something very similar to this on a SQL Server DB. That being said, I'm still a bit unclear. I want to update a value in table B based on information in Table A. So if TABLEA.COLUMN3 = 456, set TABLEB.COLUMN5 to 'ABC'. I'm setting the value in Table B to a literal value, not to a value that comes from TABLEA. Does that make sense? If i were just doing a select on these tables it would look like:

select TABLEA.COLUMN1 as a_column1,TABLEB_column1 as b_column1
from TABLEA
left outer join TABLEB
on TABLEA.COLUMN2 = TABLEB.COLUMN2
where TABLEA.COLUMN3 = 123

Based on the above select, I want to update all TABLEB.COLUMN5 values with 'ABC'
This is the best money I have ever spent. I cannot not tell you how many times these folks have saved my bacon. I learn so much from the contributors.
rwheeler23
slightwv (䄆 Netminder)

>>Okay, a real life example is attached.

Don't see an attachment.

If you don't want to use Merge, can you not use the nested select like the selected answer in your other question?
cbridgman

ASKER
The nested select is a little complex for me. I know there is an easier way. The example should now be attached.
Real-Life-Example.docx
ASKER CERTIFIED SOLUTION
slightwv (䄆 Netminder)

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.
cbridgman

ASKER
One last question for you. What if I only want to update when the shop is ELECTRICAL. I wouldn't use a CASE statement. How would I do that?
⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.
Sean Stuber

you would put that condition as a where clause on the update statement

or, if using merge  inside the USING condition
cbridgman

ASKER
thanks for your help
slightwv (䄆 Netminder)

Wouldn't a point split have been in order?
Your help has saved me hundreds of hours of internet surfing.
fblack61
cbridgman

ASKER
Yes,

I didn't pay attention to who was answering. Can I split them now or is it too late.