Update MS Access db with SQL that includes select statement

by

Summary

Updating a Microsoft Access database with SQL that includes a subquery in the SET clause is not directly supported. The provided query fails due to Access’s limitations with subqueries in UPDATE statements.

Root Cause

  • Microsoft Access does not allow subqueries in the SET clause of an UPDATE statement.
  • The query attempts to assign the result of a subquery to [CardID], which Access cannot process.

Why This Happens in Real Systems

  • Access SQL limitations: Access uses a subset of SQL that restricts complex subquery usage.
  • Syntax incompatibility: The query structure is valid in systems like MySQL or SQL Server but not in Access.

Real-World Impact

  • Data update failures: Critical updates may fail, leading to inconsistent or incorrect data.
  • Development delays: Engineers must rework queries or use workarounds, increasing development time.

Example or Code (if necessary and relevant)

-- Incorrect Access query
UPDATE [Objects] 
SET [Object] = 1, 
    [CardID] = (SELECT id FROM Oracle WHERE name = 'mycard'), 
    [CardID2] = NULL, 
    [SetID] = 1034, 
    [LangID] = 1, 
    [Version] = '', 
    [Name] = 'mycard' 
WHERE id = 502561;

How Senior Engineers Fix It

  • Use a multi-step approach:

    1. Execute the subquery separately to retrieve the value.
    2. Use the result in a subsequent UPDATE statement.

  • Example workaround: 

    -- Step 1: Retrieve the value
SELECT id INTO @CardID FROM Oracle WHERE name = 'mycard';

-- Step 2: Perform the update
UPDATE [Objects] 
SET [Object] = 1, 
    [CardID] = @CardID, 
    [CardID2] = NULL, 
    [SetID] = 1034, 
    [LangID] = 1, 
    [Version] = '', 
    [Name] = 'mycard' 
WHERE id = 502561;

Why Juniors Miss It

  • Assumption of SQL universality: Juniors often assume SQL syntax works identically across all databases.
  • Lack of platform-specific knowledge: Limited awareness of Access’s SQL limitations leads to incorrect query construction.
  • Overlooking documentation: Failure to consult Access-specific SQL documentation results in trial-and-error approaches.

Leave a Comment