• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 454
  • Last Modified:

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!!
0
-Darvin-
Asked:
-Darvin-
  • 5
  • 4
  • 4
  • +1
2 Solutions
 
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

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

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

0
 
-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 ;
0
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
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

0
 
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.
0
 
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.
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.

Take care!
0
 
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.
0
 
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.
0
 
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?
0
 
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

Featured Post

Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

  • 5
  • 4
  • 4
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now