update query returning multiple values but selecting from them 1

[cbh35711]

So i've got a query...

update indirect_customerb  b
set (b.class_of_trade) =
(select a.classoftrade
from gpo_memberships a where a.dea = b.industry_id)

whose subquery returns multiple values, which it should. But what i would like it to do is pick from those returned values based on this table:

Y00	1
W00	2
Q00	3
Z00	4
T40	5
D00	6
K00	7
M00	8
I00	9
T50	10
O00	11
N00	12
C00	13
L20	14
C10	15
J00	16
L00	17
F71	18
F70	19
H00	20

So, let's say the subquery returned Y00 and W00. It would select the lower number in that table to use in the subquery, Y00 in this case.


Thanks for your help guys,

Chris

 

[SantaMufasa]

...the subquery returned Y00 and W00. It would select the lower number in that table to use in the subquery, Y00 in this case.

In what way, Chris, is "Y00" lower than "W00"?


If we can understand this issue, then I'm sure that we can produce the code to use.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I can provide you with low-cost, remote Database Administration services: see our website and contact me via www.dasages.com]

 

[cbh35711]

The table I showed has values to the right of the Y00. Y00 is first in that list W00 is second.
Does that make more sense?

Thanks,

Chris

 

[SantaMufasa]

So, could you please clarify the name of the table that contains the "Y00" and "W00" values and the column names of that table?

Thanks,

Mufasa
(aka Dave of Sandy, Utah, USA)
[I can provide you with low-cost, remote Database Administration services: see our website and contact me via www.dasages.com]

 

[cbh35711]

Oh sure.

COT_importance is the table's name.
Name, Importance are the two columns in the table.

Thanks Dave,

Chris

 

[SantaMufasa]

I'm sorry, Chris, for the delay and the continued questions, but for me to help you to the optimal level, I need to clearly understand the key components of the tables involved in your need.

So far, I know that you are updating "indirect_customerb.class_of_trade" with contents from "gpo_memberships.classoftrade", based upon matching "gpo_memberships.dea" with "indirect_customerb.industry_id".

I also know that "COT_importance.name" must match with something (so that we can derive via "Importance", which "...Name" to use, but you haven't told me how "...Name" matches up with anything in the "indirect_customerb" or "gpo_memberships" tables.

Please resolve this last question for me.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I can provide you with low-cost, remote Database Administration services: see our website and contact me via www.dasages.com]

 

[cbh35711]

Thanks for your help Dave.

The Subquery will return multiple values because the dea is listed multiple times and each of those may or may not have different classoftrade values(Y00,W00). I've weight those in the table I showed you. So, if there are two different COTs from the subquery, then it would look at the value in the table. From the top down provide the first one it hits.
Example:

The subquery provides W00, N00, M00, but it needs to return only one value, so it uses the table:

Y00    1
W00    2
Q00    3
Z00    4
T40    5
D00    6
K00    7
M00    8
I00    9
T50    10
O00    11
N00    12
C00    13
L20    14
C10    15
J00    16
L00    17
F71    18
F70    19
H00    20

From that table:
W00=2
N00=12
M00=8

W00 is the lowest of the three values given by the table from the returned result of the subquery from before. Then it updates and replaces indirect_customerb.class_of_trade with the found value of W00.

I appreciate your assistance Dave. I hope i'm explaining this well enough.

Thanks again,

Chris

 

[SantaMufasa]

Sorry for the delay...I had a couple of other matters to which I had to attend.

Here, then, is code that should work for you. I do not have the tables resident on my Oracle database, therefore, I could not check for syntax (or run-time or logic) errors. I have also corrected your code not to update INDIRECT_CUSTOMERB cells to NULL if there is no match between “a.dea” and “b.industry_id” by my adding the "WHERE exists..." clause:

update indirect_customerb b
   set (b.class_of_trade) =
       (select a.classoftrade
          from gpo_memberships a
              ,(select name,importance
                  from COT_importance) c
         where a.dea = b.industry_id
           and a.classoftrade = c.name
           and c.importance = (select min(importance)
                                 from COT_importance
                                where c.name = name)
 WHERE exists (select ‘foo’ from gpo_memberships c
                where c.dea = b.industry_id)

Let us know if this does what you want.


Mufasa
(aka Dave of Sandy, Utah, USA)
[I can provide you with low-cost, remote Database Administration services: see our website and contact me via www.dasages.com]

 

[cbh35711]

Thanks very much for your help Dave. I'm about to head home for the day, but I'll use it tomorrow.
Thanks for the not updating to null, I've been trying to figure that one out for awhile.

Have a good evening Dave,

Chris

 

[cbh35711]

So I've tried the code.
I added a right parenthesis on the fourth line before a. That's where it should go right?

Sadly, as opposed to missing right parenthesis, it's telling me that it's not ended properly. :-/
Any ideas?
Question...the ending where clause, why foo? Am i supposed to replace it?

Thanks for your help Dave,

Chris

 

[SantaMufasa]

Chris,

My bad (on the missing paren)...it should appear just before the "WHERE exists":

update indirect_customerb b
   set (b.class_of_trade) =
       (select a.classoftrade
          from gpo_memberships a
              ,(select name,importance
                  from COT_importance) c
         where a.dea = b.industry_id
           and a.classoftrade = c.name
           and c.importance = (select min(importance)
                                 from COT_importance
                                where c.name = name)
       )
 WHERE exists (select 'foo' from gpo_memberships c
                where c.dea = b.industry_id)

That's what I get for not building some test tables to confirm the syntax for the code.

As to 'foo'...you can leave it 'foo' or replace it with any non-NULL string. "WHERE exists..." means, "Did anything return from the query?"...It means "Perform an UPDATE on the current row only IF a match occurred (thus returning 'foo') between tables 'gpo_memberships' and 'indirect_customerb' (the table you are updating)."

The "WHERE exists..." clause is the most esoteric of all of Oracle's constructs. Once you understand it (and how it works), it is remarkably powerful and much faster than alternative constructs (such as the "IN" operator).

Let me know if you have additional follow-up questions.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I can provide you with low-cost, remote Database Administration services: see our website and contact me via www.dasages.com]

 

[cbh35711]

Thanks for the explanation Dave, very helpful.
Now i can stop making tables with just the data i'm looking to update from...I knew there had to be an easier way but couldn't find it. Thanks!

And I apologize for my continue incompetence...
It's saying that a single row subquery is returning more than one row. :-/

Any thoughts?

Thanks Dave,

Chris

 

[SantaMufasa]

Chris,

Not to worry, Bud. None of us was born with any knowledge of this stuff, so we all had to learn about it at some point...Perhaps I started earlier than you did, and you'll be teaching this stuff to others in the near future.

You can solve this problem without even troubleshooting why it is returning multiple rows, by making the following code adjustment (appearing in bold):

update indirect_customerb b
   set (b.class_of_trade) =
       (select [b]min([/b]a.classoftrade[b])[/b]
          from gpo_memberships a
              ,(select name,importance
                  from COT_importance) c
         where a.dea = b.industry_id
           and a.classoftrade = c.name
           and c.importance = (select min(importance)
                                 from COT_importance
                                where c.name = name)
       )
 WHERE exists (select 'foo' from gpo_memberships c
                where c.dea = b.industry_id);

Let us konw if this solves the problem and how this all works for you.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I can provide you with low-cost, remote Database Administration services: see our website and contact me via www.dasages.com]

 

[cbh35711]

Thanks for the vote of confidence.
Well...I stopped getting errors when i ran it...but it's still running :-/
about 2 minutes now...
Gpo_memberships has about 42000 rows, and indirect_customer has 14000 rows. Should it being running for this long...about 4 minutes now

Thanks Dave,

Chris

 

[SantaMufasa]

The first thing to look for when performance is an issue is indexes. In this case, you should ensure that you have indexes on the following columns:

gpo_memberships.dea
gpo_memberships.classoftrade
indirect_customerb.industry_id
COT_importance.importance
COT_importance.name

Which of the above indexes do not exist?

Mufasa
(aka Dave of Sandy, Utah, USA)
[I can provide you with low-cost, remote Database Administration services: see our website and contact me via www.dasages.com]

 

[cbh35711]

After writing a long message post it finished the query. I just never let it finish, figured something was going wrong.
But it went fine, worked perfectly. Thank you very much for your assistance.
One follow up question, what exactly does indexing do?

Thanks Dave,

Chris

 

[SantaMufasa]

Chris,

To explain how indexing speeds things up, I'll use an analogy:

Let's pretend that you are taking a "U.S. History" class from me. The text for the class is a 900-page opus magnum entitled, "A Comprehensive History of the United States." I have given you an assignment to provide responses to these research issues:

1) List all of the page references to Raleigh, North Carolina.
2) List all of the page references involving the Attorney General of the U.S.
3) List all of the page references that refer to Great Britain as it relates to U.S. History.

Presuming that you resolve these research issues independently of one another, and presuming that there is no index for the volume, then you will end up reading 900 pages three separate times.

If, instead, there is a topical index, you need to read entries that reside on, at most, three pages in the volume.

What a difference in level of effort, right?

All of the above applies equally to indexes on tables: If you are looking for specific values in a table, indexing can reduce manifold the effort to locate specific data values in a table. I have seen indexes reduce queries from 6 hours to sub-second response.

Let us know if you have more follow-on questions.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I can provide you with low-cost, remote Database Administration services: see our website and contact me via www.dasages.com]

 

[cbh35711]

Wow that rocks.
I think this should be my last post...on this thread anyway.

Thanks for all your help,

Chris