[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Oracle SQL update statement with two table joins

Posted on 2014-08-13
20
Medium Priority
?
2,388 Views
Last Modified: 2014-08-14
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
0
Comment
Question by:-Darvin-
  • 9
  • 9
  • 2
20 Comments
 
LVL 78

Expert Comment

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

Accepted Solution

by:
slightwv (䄆 Netminder) earned 1800 total points
ID: 40258587
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
 

Author Comment

by:-Darvin-
ID: 40258589
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
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
LVL 29

Expert Comment

by:MikeOM_DBA
ID: 40258592
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
 
LVL 78

Expert Comment

by:slightwv (䄆 Netminder)
ID: 40258594
Should be able to add the joined query in the MERGE statement I posted.
0
 
LVL 29

Assisted Solution

by:MikeOM_DBA
MikeOM_DBA earned 200 total points
ID: 40258598
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
 
LVL 78

Expert Comment

by:slightwv (䄆 Netminder)
ID: 40258616
Same JOIN syntax as above if you go with the MERGE statement.
0
 

Author Comment

by:-Darvin-
ID: 40258621
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
 
LVL 78

Expert Comment

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

Expert Comment

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

Author Comment

by:-Darvin-
ID: 40259265
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
 
LVL 78

Expert Comment

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

Author Comment

by:-Darvin-
ID: 40259312
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
 
LVL 78

Expert Comment

by:slightwv (䄆 Netminder)
ID: 40259352
Can you run anonymous pl/sql blocks in this app?

begin
   null;
end;
/
0
 

Author Comment

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

Author Comment

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

Author Comment

by:-Darvin-
ID: 40259411
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
 
LVL 78

Assisted Solution

by:slightwv (䄆 Netminder)
slightwv (䄆 Netminder) earned 1800 total points
ID: 40259489
>>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
 

Author Comment

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

Author Closing Comment

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

Featured Post

Prep for the ITIL® Foundation Certification Exam

December’s Course of the Month is now available! Enroll to learn ITIL® Foundation best practices for delivering IT services effectively and efficiently.

Question has a verified solution.

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

Truncate is a DDL Command where as Delete is a DML Command. Both will delete data from table, but what is the difference between these below statements truncate table <table_name> ?? delete from <table_name> ?? The first command cannot be …
Have you ever had to make fundamental changes to a table in Oracle, but haven't been able to get any downtime?  I'm talking things like: * Dropping columns * Shrinking allocated space * Removing chained blocks and restoring the PCTFREE * Re-or…
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 how to recover a database from a user managed backup
Suggested Courses
Course of the Month18 days, 11 hours left to enroll

834 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