CODEWITHSUNDEEP
sqlms-access
davidjwest
davidjwest

Reputation: 546

Access update query using two tables

I am a novice when it comes to Access SQL - I have two tables, (Master and Extract) I need to update the Master table where the same Case exists (this is the unique key on both tables) but only if the Case Text is "NA".

UPDATE Master 
SET    ( 'Master.Date Closed' )
     = (Date())
FROM   Extract
WHERE  ('Master.Case' = 'Extract.Case' AND 'Extract.Clarification Case Text' = "NA");

I am not sure how I get the Case ID into the query, how the structure would look etc.

So if the Case ID appears in both tables and the Clarification Case Text is "NA" then put today's date into Master.Date Closed.

Thanks for any help with this.

Upvotes: 0

Views: 83

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269513

I think you intend this:

UPDATE Master 
    SET [Date Closed] = Date()
    WHERE EXISTS (SELECT 1
                  FROM Extract
                  WHERE Master.Case = Extract.Case AND
                        Extract.[Clarification Case Text] = "NA"
                 );

Note: Only use single and double quotes for string and date constants. The escape character for field and column names is the square braces. Although other characters can be used, they tend to be confusing -- either to humans or to the SQL parser.

Upvotes: 1

Related Questions