We value your feedback.
Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!
COURSE of the MONTH
8 days, 12 hours left to enrollBecome a Premium Member and unlock a new, free course in leading technologies each month.
Solved
Oracle SQL update one table based on resulting information from another
Posted on 2014-01-09
There are two tables involved. I am going to list each table and the fields involved in this problem and then write some pseudo code so you can get an idea of what I am trying to accomplish.
SHIP_TO
ARCUSTO_ID
DIVISION_ID
ARCUSTO
ID
CUSTNO
EPLANT_ID
UPDATE SET SHIP_TO.DIVISION_ID = 17 WHERE
SHIP_TO.DIVISION_ID IS NULL AND ARCUSTO.EPLANT_ID = 20 AND ARCUSTO.CUSTNO LIKE '*-30'
UPDATE SET SHIP_TO.DIVISION_ID = 16 WHERE
SHIP_TO.DIVISION_ID IS NULL AND ARCUSTO.EPLANT_ID = 20 AND ARCUSTNO.CUSTNO LIKE '*-20'
I left out the joins from ship_to.arcusto_id to arcusto.id because I am not sure how that works in this instance. I am quite comfortable updating data in a single table but when it comes to this type of problem I can't understand the resulting code to make it happen. There usually is a lot of aliases going on and I just can't make sense of it. If someone could explain the answer when the solution is found I would appreciate it. Or send me to some documentation or video so I can actually learn how this works because it keeps coming up! I feel that if I could just get a 15 minute tutorial explaining what the code structure is doing / meaning is all about I would be able to do these types of procedures myself.
Thanks in advance!!
SHIP_TO
ARCUSTO_ID
DIVISION_ID
ARCUSTO
ID
CUSTNO
EPLANT_ID
UPDATE SET SHIP_TO.DIVISION_ID = 17 WHERE
SHIP_TO.DIVISION_ID IS NULL AND ARCUSTO.EPLANT_ID = 20 AND ARCUSTO.CUSTNO LIKE '*-30'
UPDATE SET SHIP_TO.DIVISION_ID = 16 WHERE
SHIP_TO.DIVISION_ID IS NULL AND ARCUSTO.EPLANT_ID = 20 AND ARCUSTNO.CUSTNO LIKE '*-20'
I left out the joins from ship_to.arcusto_id to arcusto.id because I am not sure how that works in this instance. I am quite comfortable updating data in a single table but when it comes to this type of problem I can't understand the resulting code to make it happen. There usually is a lot of aliases going on and I just can't make sense of it. If someone could explain the answer when the solution is found I would appreciate it. Or send me to some documentation or video so I can actually learn how this works because it keeps coming up! I feel that if I could just get a 15 minute tutorial explaining what the code structure is doing / meaning is all about I would be able to do these types of procedures myself.
Thanks in advance!!
[X]
Welcome to Experts Exchange
Add your voice to the tech community where 5M+ people just like you are talking about what matters.
- Help others & share knowledge
- Earn cash & points
- Learn & ask questions
-
5
4
4- +1
14 Comments
Active today
I believe that these statements should do what you are looking for:
UPDATE ship_to a
SET division_id = 17
WHERE division_id IS NULL
AND EXISTS (SELECT 1
FROM arcusto b
WHERE a.arcusto_id = b.id
AND eplant_id = 20
AND custno LIKE '%-30');
UPDATE ship_to a
SET division_id = 16
WHERE division_id IS NULL
AND EXISTS (SELECT 1
FROM arcusto b
WHERE a.arcusto_id = b.id
AND eplant_id = 20
AND custno LIKE '%-20');
Active today
update SHIP_TO s
set s.DIVISION_ID = 17
where s.DIVISION_ID is null
and exists (select 1
from ARCUSTO a
where a.ID = s.ARCUSTO_ID
and a.EPLANT_ID = 20
and a.CUSTNO like '*-30');
update SHIP_TO s
set s.DIVISION_ID = 16
where s.DIVISION_ID is null
and exists (select 1
from ARCUSTO a
where a.ID = s.ARCUSTO_ID
and a.EPLANT_ID = 20
and a.CUSTNO like '*-20');
Both solutions return 'invalid character' when I run them. I think they are the right solutions but something is missing. I have already tried substituting % instead of * and that isn't it. Usually this is the result of missing some ;
Active today
If I understand what you want to do I believe you can do it all in a single statement.
See if the test case below works for you. If not please add to it and explain where it is failing.
See if the test case below works for you. If not please add to it and explain where it is failing.
--drop table ship_to purge;
create table ship_to(arcusto_id number, division_id number);
--drop table arcusto purge;
create table arcusto(id number, custno varchar2(20), eplant_id number);
insert into ship_to values (1,null);
insert into ship_to values (1,1);
insert into ship_to values (2,null);
insert into arcusto values(1, '11-30', 20);
insert into arcusto values(2, '11-20', 20);
commit;
merge into ship_to s
using (
select * from arcusto where eplant_id=20 and (custno like '%-30' or custno like '%-20')
) a
on ( s.arcusto_id = a.id )
when matched then
update set s.division_id = case when a.custno like '%-30' then 17 else 16 end where division_id is null
;
--after merge
select * from ship_to;
Active today
invalid character on what line?! did you copy & paste the code WITH the line numbers perhaps?! More info about the error please.
Apart from that, slightwv's solution works, too if you want to do the updates in a single statement. And it's a bit more elegant ;-)
Apart from that, slightwv's solution works, too if you want to do the updates in a single statement. And it's a bit more elegant ;-)
The line numbers didn't come along.
I wish I had more information to share other than invalid character. That's all I get out of the utility I have to use in this case. I have seen this problem before with other solutions I have been given by experts. I'll do some digging and see what was done to fix that as well as try the code in sql plus and see if I can get a more specific result.
Give me a little time and I'll get back to you all.
I wish I had more information to share other than invalid character. That's all I get out of the utility I have to use in this case. I have seen this problem before with other solutions I have been given by experts. I'll do some digging and see what was done to fix that as well as try the code in sql plus and see if I can get a more specific result.
Give me a little time and I'll get back to you all.
Active today
>>out of the utility I have
What is the utility? Do you not have sqlplus? If not, I encourage you to get it. 3rd party tools can have syntax issues with perfectly valid SQL.
What is the utility? Do you not have sqlplus? If not, I encourage you to get it. 3rd party tools can have syntax issues with perfectly valid SQL.
The utility is called DataDictionary it's from our ERP provider that interfaces with Oracle. I 'have' to use it in this case but we do also have sqlplus.
Anyway, I was able to fix the problem. Your right 3rd party tools do mess with perfectly valid code and I do believe all three of you have valid solutions. I was able to prove out both johnsone and alex140181 solutions. The invalid charecter it was complaining about was the ; at the end. The other problem I found is the match wasn't working with %-30. But if I did %-30% then they matched and worked. So, that is a valid solution as well.
slightwv I do appreciate your solution but I wasn't able to get it to work in any capacity by removing ; or changing the %'s. I am sure it is a valid solution too but I just couldn't get it to work in my environment where the others did.
I also like johnsone's solution best because I can understand it and whats going on a bit more than the rest. He was also the first responder with a valid solution so will get the most points.
Thanks for being heros today folks and making me look good.
Take care!
Anyway, I was able to fix the problem. Your right 3rd party tools do mess with perfectly valid code and I do believe all three of you have valid solutions. I was able to prove out both johnsone and alex140181 solutions. The invalid charecter it was complaining about was the ; at the end. The other problem I found is the match wasn't working with %-30. But if I did %-30% then they matched and worked. So, that is a valid solution as well.
slightwv I do appreciate your solution but I wasn't able to get it to work in any capacity by removing ; or changing the %'s. I am sure it is a valid solution too but I just couldn't get it to work in my environment where the others did.
I also like johnsone's solution best because I can understand it and whats going on a bit more than the rest. He was also the first responder with a valid solution so will get the most points.
Thanks for being heros today folks and making me look good.
Take care!
Active today
>> but I just couldn't get it to work in my environment where the others did.
Likely the tool not keeping up with valid syntax.
Likely the tool not keeping up with valid syntax.
Active today
I also like johnsone's solution best because I can understand it and whats going on a bit more than the rest. He was also the first responder with a valid solution so will get the most points.
In the end it's up to you on how you'll distribute the points, BUT johnsone's solution is exactly the same what I posted with 2 minute delay ;-) I am really not after the points, but there is a Code of Conduct here at EE (slightwv may help me with the appropriate links) and I suggest you'd better take a look at them.
Moreover, it would be more than fair, that slightwv would get "some" points for his efforts (for his solution is the best IMHO)...
Kind regards,
Alex
I don't disagree but I couldn't prove out slightlywv's solution and I feel badly for not awarding his efforts. Points have already been allocated however and I don't see a way to re-distribute. I'm sorry if I messed it up I was trying to do the right thing.
Active today
I'm fine with no points since it wouldn't work in the tool used.
I'm also OK with how the points were awarded.
-Darvin-,
Can you confirm the * wildcard is correct for the tool?
I'm also OK with how the points were awarded.
-Darvin-,
Can you confirm the * wildcard is correct for the tool?
Active today
I'm sorry if I messed it up I was trying to do the right thing.No problem at all, keep calm ;-)
It's OK for all of us, I suppose.
Thanks slightwv,
No, * is not the correct wildcard and I think that was my fault for putting it in my pseudo code to begin with (I should have known better). johnsone did correct that and used % instead.
Still I couldn't get a match on %-20 or %-30. (Code ran but didn't change anything).
But adding the % at the end provided the match and does work for our environment. The only - anything occurs at the end so it works fine.
No, * is not the correct wildcard and I think that was my fault for putting it in my pseudo code to begin with (I should have known better). johnsone did correct that and used % instead.
Still I couldn't get a match on %-20 or %-30. (Code ran but didn't change anything).
But adding the % at the end provided the match and does work for our environment. The only - anything occurs at the end so it works fine.
Featured Post
Veeam is happy to provide a free NFR license for one year. It allows for the non‑production use and valid for five workstations and two servers. Veeam Agent for Linux is a simple backup tool for your Linux installations, both on‑premises and in the public cloud.
Question has a verified solution.
If you are experiencing a similar issue, please ask a related question
This post first appeared at Oracleinaction (http://oracleinaction.com/undo-and-redo-in-oracle/)by Anju Garg (Myself).
I will demonstrate that undo for DML’s is stored both in undo tablespace and online redo logs. Then, we will analyze the reaso…
From implementing a password expiration date, to datatype conversions and file export options, these are some useful settings I've found in Jasper Server.
This video shows how to Export data from an Oracle database using the Original Export Utility. The corresponding Import utility, which works the same way is referenced, but not demonstrated.
Via a live example, show how to restore a database from backup after a simulated disk failure using RMAN.
Suggested Courses
Course of the Month8 days, 12 hours left to enroll
764 members asked questions and received personalized solutions in the past 7 days.
Join the community of 500,000 technology professionals and ask your questions.