How to update lots of records at once, like raising prices by 10%

Make an update query. In Base by Zaweba you set the new value for a field as an expression, such as [Price] * 1.1 for a ten per cent rise, add conditions if only some records should change, and run it. Base tells you how many records it will change and asks before going ahead.

That beats editing a column by hand, which is slow and easy to get wrong halfway down.

Building the query

Start a query on the table you want to change and make it an update query. For each field to change, give the new value as an expression. The field's current value is available in square brackets, so [Price] * 1.1 means "the price now, times 1.1". Other examples: [Stock] + 50 to add fifty to every stock level, or a fixed value such as Archived to set a status field.

Then add conditions to choose which records change. Only one category. Only prices under a certain figure. Only members whose renewal date has passed. Leave the conditions empty and every record in the table is updated, so double-check that is what you mean.

Check it before it runs

A good habit is to build an ordinary select query with the same conditions first, run it, and look at the list. If those are the records you want to change, turn it into the update.

When the update query runs, Base says how many records it is going to change and asks you to confirm. If the number looks wrong, say no and look at the conditions again. A rise meant for one category that reports two thousand records is a sign something is missing.

Before any big change, it is also worth pressing File > Back up now. Base copies the database automatically when you open it, but a copy taken a moment earlier is the easiest undo there is.

When Base refuses

If the new values won't fit the field, the update changes nothing at all and says why. That might be a result too long for a text field, a value that breaks a validation rule you set (such as a price above a maximum), or a calculation that turns a number field into nonsense. It's all or nothing, so you never end up with half a table updated.

Rounding money

Multiplying prices by 1.1 often produces awkward pennies. If you want tidy prices, decide on the rounding before you run the update, and check a few results in a select query first. Base works out exactly what the expression says, so the expression has to say what you mean.

Other action queries

Update is one of four action queries in Base. Delete removes records that match conditions. Append adds records from one table into another, matching columns by name or by an Append to choice, which suits moving last year's orders into an archive table. Make table creates a new table from a query's results.

All of them say how many records they will change and ask first. Once saved, you can run one by double-clicking it in the navigation pane, which makes a regular job (the annual price rise, say) a two-click task.

Action queries stay in the desktop app. If you export the database as a website with File > Export to website, Base by Zaweba leaves them out, so nobody can run a bulk change from a browser.