How do I perform update query with subquery in Access?

by

Straight answer: you can’t. The Access Database Engine simple does not support the vanilla SQL-92 scalar subquery syntax even when in its own so-called ANSI-92 Query Mode.

You are forced to use its own proprietary syntax which does not enforce the scalar requirement i.e. is unsafe and will pick a value arbitrarily and silently**. Further, beyond simple constructs it does not work at all, most notably where your subquery (if you were allowed to use one in the first place) uses an set function (MAX, SUM, etc) — see this article for some really unsatisfactory workarounds.

Sorry to be negative but this is really basic syntax and I can’t understand why the Access team haven’t gotten around to fixing it yet. It is the undisputed number one reason why I can’t take the Access database engine seriously anymore.

To demonstrate the unsafe behavior of the Access proprietary UPDATE..JOIN..Set syntax

CREATE TABLE Users
( 
  User_ID CHAR( 3 ) NOT NULL,
  Company_ID CHAR( 4 ) NOT NULL,
  UNIQUE ( Company_ID, User_ID ) );

CREATE TABLE VendorRegKeys
  CreatedBy_ID CHAR( 3 ) NOT NULL UNIQUE,
  Company_ID CHAR( 4 ) );

INSERT INTO Users VALUES ( 'Kip', 'MSFT' );
INSERT INTO Users VALUES ( 'Kip', 'AAPL' );

INSERT INTO VendorRegKeys VALUES ( 'Kip', NULL );

UPDATE VendorRegKeys
INNER JOIN Users ON Users.User_ID = VendorRegKeys.CreatedBy_ID
SET VendorRegKeys.Company_ID = Users.Company_ID;

When executing the update statement within Access, the UI warns we

You are about to update 2 row(s).

despite the fact there is only one row in the VendorRegKeys table!

What happens in practise is just one of the values we will used to update the column in that single row, without a reliable way of predicting which it will be.

With Standard SQL’s scalar subquery syntax, you would get an error and the statement would fail to execute, which is arguably the desired functionality (Standard SQL’s MERGE syntax behaves this way too).

More Related Contents:

  1. sql in ms-access [closed]
  2. Join vs. sub-query
  3. Insert into … values ( SELECT … FROM … )
  4. Is it possible to insert multiple rows at a time in an SQLite database?
  5. Managing and debugging SQL queries in MS Access
  6. How do I write a full outer join query in access
  7. Difference between LIKE and ~ in Postgres
  8. What’s the difference between DoCmd.SetWarnings and CurrentDB.Execute
  9. How to do INSERT into a table records extracted from another table
  10. Multiple INNER JOIN SQL ACCESS
  11. Table-less UNION query in MS Access (Jet/ACE)
  12. Ms Access Query: Concatenating Rows through a query
  13. Difference between CTE and SubQuery?
  14. is there a group_concat function in ms-access?
  15. Does SparkSQL support subquery?
  16. Return pre-UPDATE column values using SQL only
  17. How do I implement pagination in SQL for MS Access?
  18. SQL dot notation
  19. Curly braces in T-SQL
  20. SQL to add column with default value – Access 2003
  21. How can I implement SQL INTERSECT and MINUS operations in MS Access
  22. Postgres Error: More than one row returned by a subquery used as an expression
  23. SQL Oracle LEFT JOIN and SUBQUERY error: ORA-00905: missing keyword
  24. Convert String to Date in MS Access Query
  25. MSAccess – query to return result set of earliest rows with a unique combination of 2 columns
  26. How to save the result of a SQL query into a variable in VBA?
  27. Select query with date condition
  28. Does MS access(2003) have anything comparable to Stored procedure. I want to run a complex query in MS acceess
  29. Microsoft Access – Case Query
  30. Oracle: ‘= ANY()’ vs. ‘IN ()’

Leave a Comment