Update, delete and append in one go with Base action queries

Editing one record at a time is fine until there are three hundred of them. Prices go up by ten per cent across the catalogue. Last year's cancelled bookings need clearing out. This month's orders need copying into an archive table. Base by Zaweba has action queries for jobs like these: queries that change records rather than just showing them.

There are four kinds. An update query sets new values. A delete query removes records. An append query adds records to another table. A make table query creates a new table from the results.

The four kinds in practice

An update query sets fields to new values, and those values can be expressions. [Price] * 1.1 raises every selected price by ten per cent. Add conditions as you would in any query, so only the products in one category are changed.

A delete query removes the records that match its conditions, such as bookings marked cancelled before a certain date.

An append query copies matching records into another table. Base matches columns by name, or you can choose where each one goes with the Append to setting. It suits archiving, and moving records from an import table into the real one once they are checked.

A make table query builds a new table from a query's results, which is handy for a snapshot, such as the year end stock figures kept as they were.

Safety built in

Every action query tells you how many records it will change and asks before going ahead. Read that number. If you expected forty records and Base says four thousand, a condition is missing, and you can stop there with nothing changed.

Base also refuses changes that would break the database. An update whose new values a field can't hold changes nothing at all and says why. A delete of records that other records still need is refused in the same way, with the reason. Nothing is left half done.

Building one carefully

Start as an ordinary select query. Add the tables and conditions, run it, and look at the records it shows. When the list is exactly right, change it into an action query. That way you've seen the records that will be touched before you touch them.

Take a backup before any large change. File > Back up now makes a copy in moments, and if you later find the update was wrong, you have the database as it was.

Running and keeping them

Saved action queries appear in the navigation pane, and double-clicking one runs it. That makes regular jobs, like a monthly archive, quick to repeat, but it also means a stray double-click can start one. Base's count and question give you the chance to say no, so read them every time.

Action queries stay in the desktop app. They are not exported to the website, so people using the browser version can't run a bulk update or delete. That suits most databases: bulk changes are a job for whoever looks after the data, not for everyone with a login.

Use them for the dull, repetitive changes, and keep them well named, so it is obvious from the navigation pane what each one does before anyone runs it.