Access Update / FROM query

[springhill23]

Hi,

I'm trying to port the following to access (run through OLEDB) and I
keep getting an error stating "Syntax error (missing operator) in query
expression"

update sometable set aux = x.id
from
(select id, Nz((select top 1 id from sometable b where b.parentid=0
and b.id > a.id) - 1, 99999) nextid from sometable a where parentid=0)
x
where sometable.id between x.id and x.nextid


Any thoughts? It works under SQL Server fine.

 

[Michel Walsh]

Hi,


SELECT TOP 1 can return more than one record,


Nz( multiple_record, 99999) is surely an Nz with a huge problem on its
hand.


In short, you should use Nz around a single value, not around a record (even
if there is just one record, even if there is just one field).


You may try an outer join rather than subqueries:


SELECT a.id, Nz(b.id-1, 99999)
FROM someTable as a
LEFT JOIN (SELECT id FROM someTable WHERE parentID=0) As b
ON b.id>a.id
WHERE a.parentID = 0



There is a subquery, but it is to force the pruning of most of the records
as soon as possible, before the join occur, rather than doing it in the
WHERE clause, where we would have also have to care about possible NULL
values for b.parentID, due to the LEFT join.



Hoping it may help,
Vanderghast, Access MVP

 

[MGFoster]

Hi,

I'm trying to port the following to access (run through OLEDB) and I
keep getting an error stating "Syntax error (missing operator) in query
expression"

update sometable set aux = x.id
from
(select id, Nz((select top 1 id from sometable b where b.parentid=0
and b.id > a.id) - 1, 99999) nextid from sometable a where parentid=0)
x
where sometable.id between x.id and x.nextid


Any thoughts? It works under SQL Server fine.

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

I'm surprised it works in SQL Server, since it has Nz(), a VBA function
(not an SQL Server recognized function). Also, the derived table
doesn't make much sense; it must be just an example, right.

SQL Server can use a non-ANSI standard UPDATE statement. You will have
to change the statement to Access syntax:

UPDATE some_table
SET aux = (SELECT Nz((SELECT TOP 1 id
FROM another_table As T1
WHERE T2.parent_id = 0
AND T1.id > T2.id)-1,99999) As next_id
FROM another_table As T2 WHERE parent_id = 0)

Access has a bug... er, feature, that doesn't allow non-updateable sub
queries as the value of the SET clause. IOW, the above statement
probably won't work, 'cuz the derived table may not be updateable. The
way to overcome this is to use Domain Aggregate functions (DLookup(),
etc.) or a user-defined function that returns the value that the derived
table would return.
--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBQwOPQ4echKqOuFEgEQLABQCffmI45OavUaxPBFxUG+jyxIfW0EsAnRo9
pelKVs7kiqphrhToerCyCyUn
=/inG
-----END PGP SIGNATURE-----