Exists function in Access

In SQL server, you can check if a record exists using the EXISTS function (see link: http://www.sqlteam.com/article/using-exists).

Is there an equivalent SQL command for Access 2003?

I need to run an update query on a field in one table if a linked record does not exist in another table.
LVL 4
kittenwhiskyAsked:
Who is Participating?
 
peter57rConnect With a Mentor Commented:
I guess you can do..

update table1 set somefield = "somevalue"
where not exists (select anyfield from table2 where table1.id = table2.matchfield)
0
 
hnasrCommented:
Is there an equivalent?
Try:

For integer field:

    If DCount("*", "tblToUpdate", "fldToTest=intCriteria") > 0 Then
        'code for field exists
    Else
        'code for field not exists
    End If

For string field:

    If DCount("*", "tblToUpdate", "fldToTest='strCriteria'") > 0 Then
        'code for field exists
    Else
        'code for field not exists
    End If

0
 
GRayLConnect With a Mentor Commented:
Press Alt+F11 to get to the VB Editor.  Click Help, Microsoft Visual Basic Help, Data Manipulation Language, SQL Sub Queries, and have a good read.  As you can see there is an equivalent command in Access 2003
0
 
Leigh PurvisConnect With a Mentor Database DeveloperCommented:
Though the syntax of UPDATE statements does vary somewhat between Jet and Transact SQL - so you might want to bear that in mind too.
You can use a Frustrated Outer Join instead of EXISTS (in either platform).
(Though EXISTS generally optimises every bit as well in T-SQL and sometimes better - in both).

UPDATE Table1 LEFT OUTER JOIN Table2 ON Table1.ID = Table2.ID
SET FieldName = 'Whatever'
WHERE Table2.ID Is Null

That is essentially equivalent to the format you'll have been using:
UPDATE Table1 SET FieldName = 'Whatever'
WHERE NOT EXISTS (SELECT Null FROM Table2 WHERE Table1.ID = Table2.ID)

Though to make the same statement in T-SQL would have been like:
UPDATE Table1
SET FieldName = 'Whatever'
FROM Table1 LEFT JOIN Table2 ON Table1.ID = Table2.ID
WHERE Table2.ID Is Null

Cheers.
0
 
kittenwhiskyAuthor Commented:
After reading up on subqueries (thx gray), I used a tweaked version of peter's code:

UPDATE TblToUpdate INNER JOIN TblToCheck ON TblToUpdate.LinkedFld= TblToCheck.LinkedFld SET TblToUpdate.FldToUpdate= True WHERE TblToCheck.FldToCheck = somevalue

hsnar, your method only performs the check for one record, I need to update a whole batch of records, hence the update query method.

LPurvis, tx for your suggestion for working with Nulls. As both methods are equivalent, I preferred using the Exists method.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.