访问操作必须使用可更新查询

Question

我一直在绞尽脑汁想让这个工作一整天，它几乎在那里，但我得到了可怕的"操作必须使用可更新的查询"。经过多次修改和重写，我最终得到了以下内容:

UPDATE
    Users AS U
  INNER JOIN
    (SELECT
        UP.[Agent ID] AS AgentID,
        UP.[Last Name] AS LastName,
        UP.[First Name] AS FirstName,
        UP.[Team ID] AS TeamID,
        UP.[Security Profile ID] AS SecProfID,
        E.EmailID,
        UP.[Username] AS [Username],
        IIF(UP.Status = 'Active', 1,0) AS [Status],
        L.LocationID,
        Nz(DD.DateID, 1) AS HireDate,
        IIF(UP.[Rehire Status] = 'Eligible', 1, 0) AS RehireEligible,
        (SELECT 
             D.DateID 
         FROM 
             DateCodes AS D 
         WHERE
             D.DateValue = Format(Now(), "Short Date")) AS ModifiedDate
    FROM
        ((UsersPaste AS UP 
        LEFT JOIN
          DateCodes AS DD
        ON UP.[Hire Date] = DD.DateValue)
       INNER JOIN
         EmailAddresses AS E
       ON UP.[Email Address] = E.EmailAddress)
      INNER JOIN
         (SELECT 
              L.LocationID, T.TimeZone, L.Country, L.State, L.City 
          FROM 
              Locations AS L 
            INNER JOIN 
              TimeZones AS T 
            ON L.TimeZoneID = T.TimeZoneID) AS LL
      ON UP.[Time Zone] = LL.TimeZone 
        AND UP.Country = LL.Country 
        AND UP.State = LL.State 
        AND UP.City = LL.City) AS UU
  ON U.AgentID = UU.AgentID 
SET
    U.LastName = UU.LastName,
    U.FirstName = UU.FirstName,
    U.TeamID = UU.TeamID,
    U.SecProfID = UU.SecProfID,
    U.EmailID = UU.EmailID,
    U.[Username] = UU.[Username],
    U.[Status] = UU.[Status],
    U.LocationID = UU.LocationID,
    U.HireDate = UU.HireDate,
    U.RehireEligible = UU.RehireEligible,
    U.ModifiedDate = UU.ModifiedDate

现在，形成UU的内部Select查询准确地输出Users表是如何设置的。在我看来，如果我错了，请纠正我，这应该与我直接加入另一个表完全相同。

我是否需要创建一个实际的表，并插入由UU形成的记录到该表中，然后基于新表更新?如果有必要，我会的，但我宁愿不那样做。有人知道还有什么方法或者我需要做什么才能让它工作吗?

Answer 1

在您的情况下，是的，我认为您需要将子查询转换为make-table查询

SELECT ... INTO UU FROM ...

，然后将UPDATE查询设置为

UPDATE Users AS U INNER JOIN UU ON ...

在简单的情况下，"可更新查询"问题可以通过使用域聚合函数来避免。例如，下面的查询拒绝运行("操作必须使用可更新的查询")

UPDATE
    Donors
    INNER JOIN
    (
        SELECT 
            DonorID,
            SUM(Amount) AS SumOfAmount
        FROM Donations
        GROUP BY DonorID
    ) AS totals
        ON Donors.ID = totals.DonorID
SET
    Donors.TotalAmount = totals.SumOfAmount

但是下面的查询可以

UPDATE Donors
SET Donors.TotalAmount = DSum("Amount", "Donations", "DonorID=" & ID)

但是，您的情况非常复杂，使用临时表可能是最好的方法。