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!
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
Do more with
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
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.
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.
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.
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.
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.
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?
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
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:
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;
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?
Premium Content
You need an Expert Office subscription to comment.Start Free Trial