0

I want to update multiple records using a single update query in MS Access. I followed the idea mentioned in this answer (https://stackoverflow.com/a/65027/2935885) about inserting multiple records using a single query.

when I try this query it works:

UPDATE my_table left join (select 'rowid' as col_1, 'updated_value' as col_2 from onerow) new_values ON my_table.col_1 = new_values.col_1 set my_table.col_2 = new_values.col_2

(onerow is a dummy empty table)

However, when I extend it to multiple new values rows, MS Access gives an error "Operation must use an updateable query".

UPDATE my_table left join (
select 'rowid1' as col_1, 'updated_value1' as col_2 from onerow 
union all 
select 'rowid2' as col_1, 'updated_value2' as col_2 from onerow 
union all 
select 'rowid3' as col_1, 'updated_value3' as col_3 from onerow 
) new_values ON my_table.col_1 = new_values.col_1 set my_table.col_2 = new_values.col_2

How to fix this? Or what's wrong in the query I made?

dsauce
  • 592
  • 2
  • 14
  • 36

1 Answers1

0

You can't use update queries with union queries, since the complete rowset needs to be updateable, and union queries are never updateable.

Consider using a sequence table instead of a dual (one row) table. A sequence table is a table filled with sequential numbers.

If you'd have a sequence table with 100 numbers, you'd be able to use something like this:

UPDATE my_table left join (
    select Choose(s.nr,'rowid1', 'rowid2', 'rowid3') as col_1, Choose(s.nr,'updated_value1','updated_value2','updated_value3')  as col_2 
    from Sequence s
    WHERE s.nr Between 1 And 3
) new_values ON my_table.col_1 = new_values.col_1 set my_table.col_2 = new_values.col_2
Erik A
  • 31,639
  • 12
  • 42
  • 67
  • A deeper understanding can help code more efficiently in the future. The details of what makes a query updateable (especially in Access) can become confusing, but in the case of UNION, the rows are not updateable because the UNION is used to combine rows from mismatched sources AND the database engine does not (and often cannot) keep track of where each row came from. Thus, it cannot determine what table and row to update. There could also be duplicate rows with inconsistent update values. In other words, the source of each row is discarded or lost in the process of combining the data. – C Perkins Sep 03 '18 at 21:55