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
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
Last Comment
cbridgman
SOLUTION
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.
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.
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'
**************************
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'
>>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?
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?
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
Real-Life-Example.docx
ASKER CERTIFIED SOLUTION
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.
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?
you would put that condition as a where clause on the update statement
or, if using merge inside the USING condition
or, if using merge inside the USING condition
ASKER
thanks for your help
Wouldn't a point split have been in order?
ASKER
Yes,
I didn't pay attention to who was answering. Can I split them now or is it too late.
I didn't pay attention to who was answering. Can I split them now or is it too late.
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.