Solved

Oracle SQL update one table based on resulting information from another

Posted on 2014-01-09
14
444 Views
Last Modified: 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!!
0
Comment
Question by:-Darvin-
[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
 
LVL 35

Accepted Solution

by:
johnsone earned 400 total points
ID: 39768026
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
 
LVL 13

Assisted Solution

by:Alexander Eßer [Alex140181]
Alexander Eßer [Alex140181] earned 100 total points
ID: 39768034
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
 

Author Comment

by:-Darvin-
ID: 39768048
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
Independent Software Vendors: We Want Your Opinion

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!

 
LVL 77

Expert Comment

by:slightwv (䄆 Netminder)
ID: 39768072
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
 
LVL 13

Expert Comment

by:Alexander Eßer [Alex140181]
ID: 39768100
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
 

Author Comment

by:-Darvin-
ID: 39768130
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
 
LVL 77

Expert Comment

by:slightwv (䄆 Netminder)
ID: 39768200
>>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
 

Author Comment

by:-Darvin-
ID: 39768272
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
 
LVL 77

Expert Comment

by:slightwv (䄆 Netminder)
ID: 39768276
>> 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
 
LVL 13

Expert Comment

by:Alexander Eßer [Alex140181]
ID: 39768289
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
 

Author Comment

by:-Darvin-
ID: 39768315
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
 
LVL 77

Expert Comment

by:slightwv (䄆 Netminder)
ID: 39768325
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
 
LVL 13

Expert Comment

by:Alexander Eßer [Alex140181]
ID: 39768334
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
 

Author Comment

by:-Darvin-
ID: 39768341
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

On Demand Webinar: Networking for the Cloud Era

Did you know SD-WANs can improve network connectivity? Check out this webinar to learn how an SD-WAN simplified, one-click tool can help you migrate and manage data in the cloud.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Note: this article covers simple compression. Oracle introduced in version 11g release 2 a new feature called Advanced Compression which is not covered here. General principle of Oracle compression Oracle compression is a way of reducing the d…
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…
Via a live example show how to connect to RMAN, make basic configuration settings changes and then take a backup of a demo database
This video shows how to copy a database user from one database to another user DBMS_METADATA.  It also shows how to copy a user's permissions and discusses password hash differences between Oracle 10g and 11g.

695 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question