Update query updates table and not query results

Question

I am trying to use an UPDATE query in SQL (Access). What I would like to do is update the results of the query without affecting the underlying table. So, keep the original table intact, but update my query results. When I attempt the following code I update my query, but also the table as well. Any ideas?

UPDATE [HRBI Query] 
SET [HRBI Query].PaySegmentMultiplier = IIF([HRBI Query].[PayGroupCountryDesc] = 'Country' AND [HRBI Query].PaySegment = 'Above top segment', 0,
   IIF([HRBI Query].[PayGroupCountryDesc] = 'Country' AND [HRBI Query].PaySegment = 'Below segment 1', 1.35,
      IIF([HRBI Query].[PayGroupCountryDesc] = 'Country' AND [HRBI Query].PaySegment = 'S1', 1.25,
        IIF([HRBI Query].[PayGroupCountryDesc] = 'Country' AND [HRBI Query].PaySegment = 'S2', 1.15,
           IIF([HRBI Query].[PayGroupCountryDesc] = 'Country' AND [HRBI Query].PaySegment = 'S3', .90, 
              IIF([HRBI Query].[PayGroupCountryDesc] = 'Country' AND [HRBI Query].PaySegment = 'S4', .60, 
                 IIF([HRBI Query].[PayGroupCountryDesc] = 'Country' AND [HRBI Query].PaySegment = 'S5', .40, 
                     PaySegmentMultiplier.PaySegmentMultiplier
                 )
              )
           )
        )
     )
  )
);

score 1 · Answer 1 · edited May 23 '17 at 11:52

1

Very interesting you used my earlier answer!

Consider using a temp table which you can generate from query results with a Make-Table query.

SELECT * INTO tmpTable FROM qry

Then, routinely run the make-table (overwriting previous version) and update query on temp table. Finally, base earlier query off new temp table or just use the temp table. Altogether, you retain original and have an amenable table.

edited May 23 '17 at 11:52

Community

1
1

answered Sep 05 '15 at 12:50

Parfait

104,375
17
94
125

Awesome. Thanks again! – Chris2015 Sep 05 '15 at 17:55

score 0 · Accepted Answer · answered Sep 05 '15 at 08:40

In Access, that is not possible using a query, neither open a recordset based on the query.

You can read the date into an array an manipulate that in code. Or use another programming language like C# and open a dataset, then use this as source for whatever you are trying to do.

Update query updates table and not query results

2 Answers2