Need SQL writing help again

[LucieLastic]

hi

I have:
WordIndex table has wordID, wordString (both unique).
WordStoryLinks has wordID, storyID.
WordUsageCounts has wordID, wordString, wordCount.

Rather than do a join to get the wordString for each count of wordID, I copied the contents of WordIndex to a 'temporary' table called WordUsageCounts which also contains a wordCount column (defaulted to 0).

I want to count the stories in chunks because the WordStoryLinks table has quite a few million records in it. We want to find out the most used words and remove any that are rubbish.

I have the following but it doesn't compile. Is it possible to do what I'm trying to do? For each range of storyIDs, I want to add the count result to the count value already in WordUsageCounts for the wordIDs returned in the select but don't know how to do the outer condition for the update

update WordUsageCounts
set wordCount = wordCount + (
select count(wsl.wordID)
from WordStoryLinks wsl
where (wsl.storyID >= 2839875) and (wsl.storyID<2845000)
group by wsl.wordID)

where wordID = wsl.wordID <---Stuck here

Error:- &quot;The column prefix 'wsl' does not match with a table name or alias name used in the query. Either the table is not specified in the FROM clause or it has a correlation name which must be used instead.&quot;


Any help gratefully received.

lou
p.s. I'm using Sybase 12.5

 

[LucieLastic]

It's okay, I have a solution:

update WordUsageCounts
set wordCount = wordCount + (
select count(wsl.wordID)
from WordStoryLinks wsl
where wsl.storyID >= 2839875
and wsl.storyID<2845000
and WordUsageCounts.wordID = wsl.wordID)
where wordid in
(select wordid from WordStoryLinks
where wsl.storyID >= 2839875
and wsl.storyID<2845000)