Oracle SQL update statement with two table joins

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
/

Select allOpen in new window

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.

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

Select allOpen in new window

Should be able to add the joined query in the MERGE statement I posted.

Same JOIN syntax as above if you go with the MERGE statement.

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

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.

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.

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.

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
/

Select allOpen in new window

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.

Can you run anonymous pl/sql blocks in this app?

begin
   null;
end;
/

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.

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.

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.

select substr(trim(custno),-3) from arcusto returns the values correctly.  I'm trying to integrate that into the code already provided.

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.