Action Queries

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.

 

Examples

 

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

>= 30;

 

Lower prices (by 5%) on items whose quantity is too high.

 

Notes

 

Running an action query reports the number of affected records in the History pane.

 

See Also

 

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

DELETE Statement

DROP TABLE Statement

DROP VIEW Statement

INSERT INTO Statement

SELECT INTO Statement

UPDATE Statement