Action queries are queries that make changes to many records at once. They are used to delete records, to update records (that is, to change values in them), to create new tables, to delete tables and to launch queries that accept a user-supplied parameter. Action queries are just like any other SQL query in that they use SQL and are written within a Manifold query component. They are given the special name "action" to highlight their activity in changing the tables or interacting with the user by virtue of the SQL commands they use.
Using the Nwind.mdb sample database:
DELETE FROM [Order Details] WHERE [Discount] = 0;
Delete records for non-discounted orders.
DROP TABLE [Categories];
Delete the Categories table (square brackets are not necessary).
PARAMETERS Threshold INTEGER;
SELECT * FROM [Order Details] WHERE Quantity > Threshold;
Select orders whose quantity exceeds a number entered by the user.
SELECT DISTINCT [City] INTO [Locations] FROM [Customers];
Select all cities from Customers into the new table (Locations). The use of the DISTINCT quantifier suppresses duplicates.
UPDATE [Order Details] SET [Unit Price] = [Unit Price]*0.95 WHERE Quantity
Lower prices (by 5%) on items whose quantity is too high.
Running an action query reports the number of affected records in the History pane.
Parameter Queries for an example of writing and launching a parameter query.
SQL in Manifold System
ALTER TABLE Statement
CREATE TABLE Statement
CREATE VIEW Statement
DROP TABLE Statement
DROP VIEW Statement
INSERT INTO Statement
SELECT INTO Statement