Re: EXISTS reserved word in FROM clause

[Michel Walsh]

Hi,


Neither Jet, neither MS SQL Server 2000 allows the vector notation:


WHERE (field1, field2) IN ( SELECT g1, g2 FROM table2 )


is not supported. You have to use the EXISTS syntax:

WHERE EXISTS( SELECT * FROM table2 WHERE field1=g1 AND field2=g2 )





Hoping it may help,
Vanderghast, Access MVP

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?