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!!
-Darvin-Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

johnsoneSenior Oracle DBACommented:
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');

Open in new window

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Alex [***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');

Open in new window


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');

Open in new window

-Darvin-Author Commented:
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 ;
Get Blueprints for Increased Customer Retention

The IT Service Excellence Tool Kit has best practices to keep your clients happy and business booming. Inside, you’ll find everything you need to increase client satisfaction and retention, become more competitive, and increase your overall success.

Download your eBook!
slightwv (䄆 Netminder) Commented:
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 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;

Open in new window

Alex [***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 ;-)
-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.
slightwv (䄆 Netminder) Commented:
>>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.
-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.

Take care!
slightwv (䄆 Netminder) Commented:
>> 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.
Alex [***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
-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.
slightwv (䄆 Netminder) Commented:
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?
Alex [***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.
-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.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.