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

cbridgman
cbridgman used Ask the Experts™
on
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
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Most Valuable Expert 2012
Distinguished Expert 2018

Commented:
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:
http://www.experts-exchange.com/questions/28646923/Oracle-Cannot-Update-To-Null.html#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.
Most Valuable Expert 2011
Top Expert 2012
Commented:
try something like this


MERGE INTO tableb b
     USING (SELECT *
              FROM tablea
             WHERE column3 IN (123, 456)) a
        ON (a.column1 = b.column2)
WHEN MATCHED
THEN
    UPDATE SET tableb.column5 = CASE a.column3 WHEN 123 THEN 'ABC' WHEN 456 THEN 'DEF' END


if you can provide some some sample data and expected results with non-generic names I can provide exact syntax with tested results.

Author

Commented:
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.
Ensure you’re charging the right price for your IT

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

Author

Commented:
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'
Most Valuable Expert 2012
Distinguished Expert 2018

Commented:
>>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?

Author

Commented:
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
Most Valuable Expert 2012
Distinguished Expert 2018
Commented:
I would still use MERGE for Oracle.  Trying to write SQL that works across platforms will likely not perform well on at least one of them...

Think about only having one hammer to drive ALL types of nails.  There is a reason there are MANY different types of hammers...  each one is made to be the best at one particular function.  Use that hammer for that function and it won't be a lot of work.

Try this:
update WOCRAFT c set craft=(
      select case shop
            when 'ELECTRICAL' then 412
            when 'PLUMBING' then 510
            when 'MECHANICAL' then 885
      end from WORKORDER w where c.wonum=w.wonum
);

Here was my test setup:
/*
drop table myWORKORDER purge;
create table myWORKORDER(WONUM varchar2(6), SHOP varchar2(20), STATUS varchar2(20));
insert into myWORKORDER values('011654','ELECTRICAL','INPROG');
insert into myWORKORDER values('011658','ELECTRICAL','INPROG');
insert into myWORKORDER values('011788','PLUMBING','WAITING');
insert into myWORKORDER values('011998','PLUMBING','INPROG');
insert into myWORKORDER values('011979','PLUMBING','INPROG');
insert into myWORKORDER values('011905','MECHANICAL','WAITING');
commit;

drop table myWOCRAFT purge;
create table myWOCRAFT(WONUM varchar2(6), TASK number, CRAFT number);
insert into myWOCRAFT values('011654',10,null);
insert into myWOCRAFT values('011654',20,null);
insert into myWOCRAFT values('011654',30,null);
insert into myWOCRAFT values('011658',10,null);
insert into myWOCRAFT values('011658',20,null);
insert into myWOCRAFT values('011658',30,null);
insert into myWOCRAFT values('011788',10,null);
insert into myWOCRAFT values('011788',20,null);
insert into myWOCRAFT values('011998',10,null);
insert into myWOCRAFT values('011979',10,null);
insert into myWOCRAFT values('011905',10,null);
commit;
*/


update myWOCRAFT c set craft=(
	select case shop
		when 'ELECTRICAL' then 412
		when 'PLUMBING' then 510
		when 'MECHANICAL' then 885
	end from myWORKORDER w where c.wonum=w.wonum
);

select * from myWOCRAFT;
rollback;

Open in new window

Author

Commented:
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?
Most Valuable Expert 2011
Top Expert 2012

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

or, if using merge  inside the USING condition

Author

Commented:
thanks for your help
Most Valuable Expert 2012
Distinguished Expert 2018

Commented:
Wouldn't a point split have been in order?

Author

Commented:
Yes,

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

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial