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.
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');
Alexander Eßer [Alex140181]Software DeveloperCommented:
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 ;
0
Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.
One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.
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.
--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 susing ( select * from arcusto where eplant_id=20 and (custno like '%-30' or custno like '%-20')) aon ( 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 mergeselect * from ship_to;
Alexander Eßer [Alex140181]Software DeveloperCommented:
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 ;-)
0
-Darvin-Author Commented:
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.
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.
0
-Darvin-Author Commented:
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.
Alexander Eßer [Alex140181]Software DeveloperCommented:
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
0
-Darvin-Author Commented:
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.
Alexander Eßer [Alex140181]Software DeveloperCommented:
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.
0
-Darvin-Author Commented:
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.
0
Question has a verified solution.
Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.
The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.
One of a set of tools we're offering as a way of saying thank you for being a part of the community.
Open in new window