Oracle SQL update statement with two table joins

I have three tables involved in this.  I need to update one table (AUTO_EFORM) and column (EPLANT_ID) based on data in another table (ARCUSTO) and column (CUSTNO).  The tables and columns are listed below for reference and needed joins.

AUTO_EFORM
EPLANT_ID (number)
CONTACT_ID (number)

CONTACT
ID (number)

ARCUSTO
CONTACT_ID (number)
CUSTNO (string)

The CUSTNO field is a string value and I want to evaluate the last three characters.
If right(custno,3) = -10 then AUTO_EFORM.EPLANT_ID = 10
If right(custno,3) = -20 then AUTO_EFORM.EPLANT_ID = 20
If right(custno,3) = -30 then AUTO_EFORM.EPLANT_ID = 30
If right(custno,3) = -40 then AUTO_EFORM.EPLANT_ID = 40
If right(custno,3) = -50 then AUTO_EFORM.EPLANT_ID = 50
-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.

slightwv (䄆 Netminder) Commented:
I don't see where the CONTACT table is used.

When updating across tables, I prefer to use MERGE.

Based on just the two tables, try this:

merge into auto_eform ae
using (select contact_id, custno
from arcusto) ac
	on (ae.contact_id = ac.contact_id)
when matched then
    update 
        set
            ae.eplant_id = case substr(ac.custno,-3)
                when '-10' then 10
                when '-20' then 20
                when '-30' then 30
                when '-40' then 40
                when '-50' then 50
                end
/

Open in new window

0
slightwv (䄆 Netminder) Commented:
The 'other' way but note there is no where clause on the update statement itself so it will update ALL rows in the table.

You can get around that by adding a where clause to the update statement but you'll hit the table again.

update auto_eform ae
set eplant_id = (
    select case substr(custno,-3)
                when '-10' then 10
                when '-20' then 20
                when '-30' then 30
                when '-40' then 40
                when '-50' then 50
                end
    from arcusto ac
    	where ae.contact_id=ac.contact_id
);

Open in new window

0

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
-Darvin-Author Commented:
I'm so sorry, my original post is wrong.

CONTACT_ID does not exist in ARCUSTO, that's why the CONTACT table is needed.

I should have said:
AUTO_EFORM
EPLANT_ID (number)
CONTACT_ID (number)

CONTACT
ID (number)
ARCUSTO_ID (NUMBER)

ARCUSTO
ID (NUMBER)
CUSTNO (string)

I need to go through the contact table to obtain the arcusto_id and find the correct CUSTNO.
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

MikeOM_DBACommented:
Or just:
UPDATE auto_eform ae
SET    eplant_id = (SELECT CASE
                             WHEN Substr(custno, -3) = '-10' THEN 10
                             WHEN Substr(custno, -3) = '-20' THEN 20
                             WHEN Substr(custno, -3) = '-30' THEN 30
                             WHEN Substr(custno, -3) = '-40' THEN 40
                             WHEN Substr(custno, -3) = '-50' THEN 50
                             ELSE NULL
                           END
                    FROM   arcusto cn
                    WHERE  cn.contact_id = ae.contact_id)  ;

Open in new window

0
slightwv (䄆 Netminder) Commented:
Should be able to add the joined query in the MERGE statement I posted.
0
MikeOM_DBACommented:
Ooops, slightwv beat me to it...
Modifed sql:
UPDATE auto_eform ae
   SET   eplant_id = (SELECT CASE
                             WHEN Substr(custno, -3) = '-10' THEN 10
                             WHEN Substr(custno, -3) = '-20' THEN 20
                             WHEN Substr(custno, -3) = '-30' THEN 30
                             WHEN Substr(custno, -3) = '-40' THEN 40
                             WHEN Substr(custno, -3) = '-50' THEN 50
                             ELSE NULL
                           END
                    FROM   contact c,
                           arcusto a
                    WHERE  c.contact_id = ae.contact_id
                      AND  a.id = c.arcusto_id);  

Open in new window


Cheers.
0
slightwv (䄆 Netminder) Commented:
Same JOIN syntax as above if you go with the MERGE statement.
0
-Darvin-Author Commented:
Ok, thanks guys. I'm getting a invalid character response but that's somewhat normal for the tool I have to use as it isn't exactly the same as what you would get using something like sqlplus.  I'll work that out and get back to this in a hour or more.  (have a meeting to hit but I am sure the solution is here).
0
slightwv (䄆 Netminder) Commented:
Some older 'tool's don't understand the new MERGE command or data warehouse functions like ROW_NUMBER/RANK.

The good old update statement is pretty standard syntax and should work.
0
slightwv (䄆 Netminder) Commented:
I don't know your situation on not having sqlplus and having to use a specific tool.

DISCLAIMER:  This is not to suggest a way around any corporate policies you have in place.

In some shops it is more about getting someone to actually install software not really anything against using software.

If the restriction is about actually installing something as opposed to actually using it, you can get sqlplus without having to install anything.

Download the Instant Client (manually create the network/admin folder, copy in your tnsnames.ora file) and you're done.  No install necessary.

It is two files:
Basic and the sqlplus add-on.

Also if you have Java (correct version) installed, I don't believe SQL Developer requires anything to be installed before you can use it.
0
-Darvin-Author Commented:
Ok guys, I have some progress here.  I was able to get the update statements from both of you to work BUT it only updated the last row in the table not all.

I wasn't able to get the merge statement to work just because I don't understand the join syntax as it relates to what was in the update statements.  I'm just not as familiar with that one.

As for SQLPlus we do have it I just can't use it for the application I am running this in.  When I took out the ; at the end they run but as I said only update the last record.
0
slightwv (䄆 Netminder) Commented:
I would check the app.

There is no where clause so it will update everything where the contact_id's match between the results.

What is this app?

>>because I don't understand the join syntax as it relates to what was in the update statements

Just add the contact table and join like above.  Inside the USING part of the MERGE.

Untested but something like:
merge into auto_eform ae
using (
select c.contact_id, a.custno
                    FROM   contact c,
                           arcusto a
                    WHERE a.id = c.arcusto_id
) ac
	on (ae.contact_id = ac.contact_id)
when matched then
    update 
        set
            ae.eplant_id = case substr(ac.custno,-3)
                when '-10' then 10
                when '-20' then 20
                when '-30' then 30
                when '-40' then 40
                when '-50' then 50
                end
/

Open in new window

0
-Darvin-Author Commented:
It's a custom application from our ERP provider and usually causes issues in cases like this where perfectly sound oracle sql just needs a little massaging to work.

-50 happens to be the last row of the table and is the only one getting updated.  If I remove that line so that the -40 is the last match of the case statement it doesn't populate that either.

So it doesn't appear to be the last row is the only row updated but more over it's having a hard time finding the matches for some reason.
0
slightwv (䄆 Netminder) Commented:
Can you run anonymous pl/sql blocks in this app?

begin
   null;
end;
/
0
-Darvin-Author Commented:
I ran that without error.

Is there a way to add wildcard to the case statements?  Similar to
custno like '%-30%'

I want to eliminate the possibility that it's not matching due to a space or something messing up the matching.
0
-Darvin-Author Commented:
Ah, seems a database problem.  If I run select substr(custno, -3) from arcusto it does not return the correct values most likely due to added spaces on the custno column (that should not be there in the first place).  Trying to correct that then this should work.
0
-Darvin-Author Commented:
Hmm, I ran
update arcusto set custno = trim(custno) (that should have removed any spaces right?)

Then I ran select substr(custno,-3) from arcusto and the only ones that come through with dashes are the -50.  Everything else is just 10,20,30,40.

Thats why they aren't matching.
0
slightwv (䄆 Netminder) Commented:
>>that should have removed any spaces right?)

Yes.  BUT, if the datatype of the column is a CHAR, it adds spaces back when you select it out.

>>Everything else is just 10,20,30,40.

Might be the char issue I mentioned above

try:
select substr(trim(custno),-3) from arcusto
0
-Darvin-Author Commented:
select substr(trim(custno),-3) from arcusto returns the values correctly.  I'm trying to integrate that into the code already provided.
0
-Darvin-Author Commented:
I hope the point spread is ok.  I ended up liking the update statements as I can understand them.  I borrowed from both experts solution to ultimately fix the problem.
0
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.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.