The CompTIA Cloud+ Basic training course will teach you about cloud concepts and models, data storage, networking, and network infrastructure.
Oracle SQL update one table based on resulting information from another
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!!
Who is Participating?
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 ;
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;
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.
>>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!
>> 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.
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.
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?
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.
Question has a verified solution.
Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.
Have a better answer? Share it in a comment.
All Courses
From novice to tech pro — start learning today.
Open in new window