Aggregate // Update Function (theoretically simple)..issue. 1

[efinnen]

Hi All, first off let me preface this posting by saying I am an Access guy who is still learning how to do some things in Oracle.. and therein lies my issue. Any help or suggestions would be greatly appreciated. I’m assuming something along these lines is possible, right??

I have a table Analysis, with has an NMBR_TRIP field and TRIPID Field. What I want do is simply populate the NMBR_TRIP field with the count of TRIPID.

The Access solution is fairly simple

UPDATE ANALYSIS SET NMBR_TRP = DCount("*","SPECIES_02_03","TRIPID=" & [TRIPID]);


And the Select query in Oracle seems to be easy

SELECT ANALYSIS.TRIPID, Count(*) AS Freq
FROM ANALYSIS
GROUP BY ANALYSIS.TRIPID;

However I want to embed that aggregate query above in an UPDATE query and I’m having problems with it.

My best guess (which doesn’t work) is something along these lines

update a
set NMBR_TRIP = FREQ
from Analysis A join
(SELECT ANALYSIS.TRIPID, Count(*) AS Freq
FROM ANALYSIS
GROUP BY ANALYSIS.TRIPID) S
on A.tripid = s.tripid


Thx
-eric

 

[SantaMufasa]

...I’m having problems with it...

Please post a copy-and-paste of the "problems" that you are having (i.e, syntax problems, run-time problems, logic problems, or other).


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]

 

[efinnen]

update a
set NMBR_TRIP = FREQ
from Analysis A join
(SELECT ANALYSIS.TRIPID, Count(*) AS Freq
FROM ANALYSIS
GROUP BY ANALYSIS.TRIPID) S
on A.tripid = s.tripid

give me a

ORA-009933: SQL Command not properly ended..

(I also get the same error with the semicolon at the end, If that matters)

 

[SantaMufasa]

Efinnen, Although I haven't tested the code, below, it should work. Please let us know the outcome:

update analysis a
set NMBR_TRIP =
   (select Count(*) from analysis s
     where a.tripid = s.tripid);

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]

 

[feherke]

Hi

update a
set NMBR_TRIP = FREQ
[red]from[/red] Analysis A join
(SELECT ANALYSIS.TRIPID, Count(*) AS Freq
FROM ANALYSIS
GROUP BY ANALYSIS.TRIPID) S
on A.tripid = s.tripid

As I saw, in Oracle the [tt]update[/tt] command does not have [tt]from[/tt] clause. Or I am wrong ?

Feherke.

http://rootshell.be/~feherke/

 

[SantaMufasa]

Feherke,

Correct...Oracle does not currently support "UPDATE...FROM".

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]

 

[efinnen]

Sorry for the slow reply, I just got out of 6 hours of meetings..

update analysis a
set NMBR_TRP =
(select Count(*) from analysis s
where a.tripid = s.tripid);


It just keeps running. I had to cancel the operation. This brought up warning bells from an earlier posting I had which Dave//SantaMufasa helped me out with with building indexes. I built an index on TRIPID. Which also just keeps running when I apply the above code.,

As far as the comments about not being able to use the FROM in my earlier sample snipped. I’m going out on a limb here and saying that the solution isn’t as simple as just deleting the FROM, right? (I also tried it to no success)

 

[SantaMufasa]

Efinnen,

If you are suffering from performance issues, then this should speed things up:

create table temp_analysis as select Count(*) cnt,tripid from analysis
group by tripid;

create index temp_analysis_idx on temp_analysis(tripid);

update analysis a
set NMBR_TRP =
   (select cnt from temp_analysis s
     where a.tripid = s.tripid);

drop table temp_analysis;

Let us know if this meets your performance needs.

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]

 

[efinnen]

Thanks for the help. It worked using the approach above, but it was very pokey (slow) 785 seconds for the query.

I just want to verify something here.. The suggested approach earlier was to effectiverly run that as four different commands, right? (Make Table, Make Index, Run Update, Delete Table )That is the approach I used. And the third step took a long time.

This is certainly do-able (since it works), but the speed certainly isn't optimal. Any thoughts on how to expedite that process? For information sake the ANALYSIS table is only about 175k records.

Thanks for any suggestions.

-eric

 

[SantaMufasa]

Eric,

Actually, from the very beginning of your issue, I've wondered what the "business need" is for your doing the UPDATE statement...The data that you are updating certainly are not properly normalised.

Since the UPDATE is the slowest performer, why don't you just lose the UPDATE altogether and, when the count(*) is necessary for business purposes, do an indexed join similar to what we did above?

Let us know your thoughts.

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]

 

[efinnen]

I don't know if this answers the question or not about the "business need" end of things or not. But I figure I should give it a shot. And to be honest I’m a little concerned that I’m doing my whole approach incorrect. But I hope I have a decent enough head on my shoulders to be doing this thing correctly.

The background/business end of t his thing is that’s I am implementing this in a GIS program that I am writing (ArcGIS) and all the sql commands get build programmatically depending on how the user clicks on forms or on the map that they are presented with. This specific application has to do with fishing effort.

So the issue is there is a “hypothetical” fishing trip (TRIPID) of ten days. During this fishing trip the boat caught 10 different species. The user queries the data but only wants the data pertaining to 3 of the species of fish. My DAYATSEA field in the table I create is currently 10 since that is how long the trip was. However when I sum up the data for this example. I will get a net sum of 30 days which is incorrect. What I want to get is get a sum of 3 (Which if you are splitting hairs is still incorrect, but less so..)

So I’m trying to generate a NMBR_TRP which effectively counts up the number of species per trip. And then recalculate the DAYSATSEA field to be equal to DAYSATSEA/NMBR_TRP.

Hopefully this explanation makes sense.

Optimally I’d like to do this in Oracle. But worst case scenario I could do this client side.

I’m used to building and killing tables all the time in geodatabases (GIS term). Is this approach considered really bad form in Oracle??

And as an added bonus, we’re not allowed to touch the structure of the inherent data (which is probably a good thing)

-eric

 

[Thargy]

effinen,

I used to do Access, before moving to Oracle. I remember gasps of horror when I casually mentioned dropping tables.

In Access, this is no big deal. However, in Oracle it causes major headaches. Stored procedures will de-compile, triggers will fail (something never available when I did Access), indexes are junked, sequences are left hanging in the wind and basically it's bad news.

Oracle plugs table names into itself much more deeply than access, so dropping tables should be avoided, if possible.

If the underlying data is hopelessly de-normalised, it may be easier (and more maintainable) to open a cursor and handle the whole mess in pl/sql. At least that way you can code defensively against the poor data structure.

Regards

Tharg

Grinding away at things Oracular

 

[efinnen]

thargtheslayer

Thansk for the heads up.. Ironically, I did get tsk-ed tsk-ed on that approach earlier today. I think the current way will be to keep the table structure but just delete everything inside it.. (TRUNCATE, I think..)

-e