RE: EXISTS reserved word in FROM clause

From: Ted Allen (anonymous_at_discussions.microsoft.com)
Date: 07/15/04 

Date: Thu, 15 Jul 2004 14:38:18 -0700

Hi Stephanie,

Well, we make a good pair because I know Access SQL
syntax fairly well, but am hardly familiar with
SQL/Oracle specifics. I'm an Engineer so I get stuck
using Access on the front end without getting to play
with SQL/Oracle on the back end.

Anyway, from what you described the query syntax that I
posted for you earlier should do what you want, unless I
made a mistake somewhere (there were a few field names
that I didn't explicitly list the table, so I revised the
following). Try pasting the following in a new query's
SQL view (to get to SQL view open a new query in design
view, close the table selection list, then go to view on
the menu and choose sql).

UPDATE rail_sw
SET rail_sw.fetype = 1
WHERE rail_sw.FENAME <> " " AND rail_sw.FENAME NOT
LIKE "*ABANDONED*" AND rail_sw.LENGTH = (SELECT Max
(VT.LENGTH) FROM rail_sw AS VT WHERE VT.FENAME =
rail_sw.FENAME);

Run the query and see what happens, I believe it will
give you what you want. To run the query click the
red "!" in design view, or double click from the query
window if the query is not open. Note that in Access
clicking the view button (the one that looks like a
table), will not run the action query, it will only
display the records that will be affected (in this case
it would just display the current values of the fetype
field for the records that would be updated - but you can
get a feel for whether or not it is the right number by
looking at the record count).

If it doesn't work, post back and let me know what
message it gave. As always, it is a good idea to have a
backup of your data before running a new action query.
Although in this case it seems that the worst that could
happen is that it would set some fetype values
incorrectly, so if they are currently all blank it
wouldn't really matter - you could always reset them.

One other thing that you could do is simplify the query
by not worrying about the FENAME criteria. You could
always filter out those names later (or reset them to
0). If you wanted to try that the sql would be:

UPDATE rail_sw
SET rail_sw.fetype = 1
WHERE rail_sw.LENGTH = (SELECT Max
(VT.LENGTH) FROM rail_sw AS VT WHERE VT.FENAME =
rail_sw.FENAME);

Post back and let me know how it goes.

-Ted Allen

>-----Original Message-----
>Thanks for replying so quickly, Ted. Unfortunately the
results of the sample query would not be what I'm looking
for.
>
>You are correct in that I am writing this as SQL
(mostly, because I don't know how to use Access's
querying functions in Design View or Wizard for something
like this.) Although actually, I started out looking for
the right syntax using Oracle. The subquery I wrote is
designed to return name and length values for records
from a table which have the longest segment of a railway
from a set of segments bearing the same name. For
instance if a portion of the table looked something like:
>
>fename length
>a 12.3
>a 9.5
>a 17.6
> 12.4
>b 6.7
>b 4.2
>
>The subquery should return the records:
>a 17.6
>b 6.7
>
>In turn, the main query will flag these segments by
setting fetype = 1. If I just run the subquery's Select,
it is returning the correct records, but I'm not sure how
to set the syntax of the main update query so that it
then locates these records for update.
>
>Hopefully this makes more sense, even if my syntax
leaves much to be desired.
>
>Thanks,
>Stephanie
>
>"Ted Allen" wrote:
>
>> Hi Stephanie,
>>
>> Are you writing this query in Access? The syntax
looks
>> different (like SQL Server?). I say this partly
because
>> you are using the % symbol for the wildcard, but
>> Access/Jet uses the * symbol.
>>
>> I think you are getting an error referencing EXISTS
>> because AFAIK that is the only time you can use
>> subqueries that return multiple fields.
>>
>> The EXISTS reserved word is used with subqueries
>> basically as a boolean that will return true if the
>> subquery returns any records, and false if not.
>> Therefore it would normally be used to set the
condition
>> for a boolean field (to be honest I never really use
>> EXISTS).
>>
>> Unless used with IN (or NOT IN) or EXISTS (or NOT
>> EXISTS), Access expects only one value for one field
to
>> be returned by a subquery. When using IN (or NOT IN)
>> Access expects one or more values for a single field.
>>
>> I get the feeling that the EXISTS statement won't
really
>> be useful in your case, rather I think it is just a
case
>> of needing to adjust your sql syntax to be compatible
>> with JET, which means breaking up your criteria to
>> compare directly with the individual fields in your
query.
>>
>> For instance, it looks like the FENAME field condition
>> could be:
>>
>> <> " " AND NOT LIKE "*ABANDONED*"
>>
>> For length it looks like you are trying to restrict
the
>> updates to only those records where the length is
equal
>> to the max length for a particular FENAME. If that is
>> the case, you need to use a correlated subquery to
>> restrict the domain of the query such as the following:
>>
>> (SELECT Max(LENGTH) FROM rail_sw AS VT WHERE VT.FENAME
=
>> rail_sw.FENAME)
>>
>> You could also use Dlookup to achieve this, but I
prefer
>> subqueries and they are often faster.
>>
>> So, if I can put this together without any typos it
would
>> look something like:
>>
>> UPDATE rail_sw
>> SET fetype = 1
>> WHERE rail_sw.FENAME <> " " AND rail_sw.FENAME NOT
>> LIKE "*ABANDONED*" AND rail_sw.LENGTH = (SELECT Max
>> (LENGTH) FROM rail_sw AS VT WHERE VT.FENAME =
>> rail_sw.FENAME)
>>
>> A couple of other notes, the comparison to the string
>> with one space, this will only restrict the query from
>> updating records where the FENAME exactly equals one
>> space. If you are trying to eliminate blank fields
you
>> may want to add the condition IS NOT NULL, and if you
>> want to eliminate empty strings, you may want the
>> condition <>"", or you could combine these by using Nz
>> (FENAME,"")<>""
>>
>> HTH, Ted Allen
>>
>>
>>
>> >-----Original Message-----
>> >Hello World,
>> >
>> >I am writing an update query that changes a column
value
>> for a group of records based on the maximum value of
>> another column. The query is:
>> >
>> >UPDATE rail_sw
>> >SET fetype = 1
>> >where (FENAME, Length) in
>> >(SELECT FENAME, MAX(LENGTH)
>> >FROM rail_sw
>> >WHERE FENAME <> ' ' AND
>> >FENAME NOT LIKE '%ABANDONED%'
>> >GROUP BY FENAME)
>> >
>> >When I try running the query, the following message
pops
>> up:
>> >You have written a subquery that can return more than
>> one field without using the EXISTS reserved word in
the
>> main query's FROM clause. Revise the SELECT statement
of
>> the subquery to request only one field.
>> >
>> >When I select Help in the pop-up window, the only
>> additional information I get is that this is an Error
>> 3306 and to contact Microsoft Product Support Services
>> for more information.
>> >
>> >Can anyone tell me about this EXISTS reserved word
and
>> how I can use it in conjunction with the subquery to
run
>> the update?
>> >
>> >Thanks,
>> >Stephanie
>> >
>> >.
>> >
>>
>.
>