Solved

Oracle SQL update one table based on resulting information from another

Posted on 2014-01-09
14
435 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-
  • 5
  • 4
  • 4
  • +1
14 Comments
 
LVL 34

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
 
LVL 76

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 76

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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 

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 76

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 76

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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

Subquery in Oracle: Sub queries are one of advance queries in oracle. Types of advance queries: •      Sub Queries •      Hierarchical Queries •      Set Operators Sub queries are know as the query called from another query or another subquery. It can …
How to Unravel a Tricky Query Introduction If you browse through the Oracle zones or any of the other database-related zones you'll come across some complicated solutions and sometimes you'll just have to wonder how anyone came up with them.  …
This video explains at a high level about the four available data types in Oracle and how dates can be manipulated by the user to get data into and out of the database.
This video shows syntax for various backup options while discussing how the different basic backup types work.  It explains how to take full backups, incremental level 0 backups, incremental level 1 backups in both differential and cumulative mode a…

759 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

Need Help in Real-Time?

Connect with top rated Experts

24 Experts available now in Live!

Get 1:1 Help Now