Problem on update query

[Franck Fouache]

Hi,
i'd like to do this query in my Access97 database :
update ADRLIVR set al_libelle=(select t_libelle from TIERS where
t_auxiliaire=al_auxiliaire) where al_libelle='' or al_libelle is null
unfortunatly it doesn't work whereas it's work well in sql2000 ...
can anybody give me the right syntax ...
thanks ...
best regards

 

[John Spencer]

Access realizes that the sub-query has the potential to return more than one
value, so it won't even attempt the update. SQL2000 doesn't care that the
potential is there, it will work as long as less than 2 values are returned.

Try the following on a copy of your table

update ADRLIVR INNER JOIN TIERS
ON t_auxiliaire=al_auxiliaire
set ADRLIVR.al_libelle= [TIERS].[t_libelle]
where al_libelle='' or al_libelle is null

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

 

[Franck Fouache]

Hmm ....
Access97 says me that kind of subquery is not supported ...
so i tried different queries like yours and this one seems to work for me :

update ADRLIVR,TIERS
set al_libelle=t_libelle
where (al_libelle='' or al_libelle is null)
and al_auxiliaire=t_auxiliaire

i thought that adrlivr,tiers was impossible ... happy to see not ...
thanks for your help

Access realizes that the sub-query has the potential to return more than
one value, so it won't even attempt the update. SQL2000 doesn't care that
the potential is there, it will work as long as less than 2 values are
returned.

Try the following on a copy of your table

update ADRLIVR INNER JOIN TIERS
ON t_auxiliaire=al_auxiliaire
set ADRLIVR.al_libelle= [TIERS].[t_libelle]
where al_libelle='' or al_libelle is null

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
.

Hi,
i'd like to do this query in my Access97 database :
update ADRLIVR set al_libelle=(select t_libelle from TIERS where
t_auxiliaire=al_auxiliaire) where al_libelle='' or al_libelle is null
unfortunatly it doesn't work whereas it's work well in sql2000 ...
can anybody give me the right syntax ...
thanks ...
best regards

 

[John Spencer]

WHOOPS! Glad you found a solution.

My suggested SQL left out the table Names in the ON clause which are
required

The SQL probably should have read.

update ADRLIVR INNER JOIN TIERS

ON ADRLIVR .al_auxiliaire= TIERS.T_auxiliaire

set ADRLIVR.al_libelle= [TIERS].[t_libelle]
where al_libelle='' or al_libelle is null

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

Hmm ....
Access97 says me that kind of subquery is not supported ...
so i tried different queries like yours and this one seems to work for me
:

update ADRLIVR,TIERS
set al_libelle=t_libelle
where (al_libelle='' or al_libelle is null)
and al_auxiliaire=t_auxiliaire

i thought that adrlivr,tiers was impossible ... happy to see not ...
thanks for your help

Access realizes that the sub-query has the potential to return more than
one value, so it won't even attempt the update. SQL2000 doesn't care
that the potential is there, it will work as long as less than 2 values
are returned.

Try the following on a copy of your table

update ADRLIVR INNER JOIN TIERS
ON t_auxiliaire=al_auxiliaire
set ADRLIVR.al_libelle= [TIERS].[t_libelle]
where al_libelle='' or al_libelle is null

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
.

Hi,
i'd like to do this query in my Access97 database :
update ADRLIVR set al_libelle=(select t_libelle from TIERS where
t_auxiliaire=al_auxiliaire) where al_libelle='' or al_libelle is null
unfortunatly it doesn't work whereas it's work well in sql2000 ...
can anybody give me the right syntax ...
thanks ...
best regards