UtterAccess HomeUtterAccess Wiki

Welcome Guest ( Log In | Register )

Custom Search
Edit Discussion
> Action Queries    
Action Queries

This page is a stub. Please help us by expanding or merging it with applicable content
This page is under consideration for merging with: Calling Action Queries, RunSQL vs Execute

Related Content:
    RunSQL vs Execute
    Calling Action Queries


Contents

Basic Examples



Delete Queries


ACE Syntax

SQL
DELETE * FROM tablename WHERE criteria

ANSI Standard Syntax

SQL
DELETE Field1, Field2 FROM tablename WHERE criteria

You can delete all the data from a given table by supplying all (*) fields and no criteria. This is useful for temporary tables:

SQL
DELETE * FROM tablename

Note that the non-standard syntax allow you to join several tables and therefore specify which rows of single table to be deleted. Only one table's content can be deleted per a delete query:

SQL
DELETE o.* FROM tblOrders AS o INNER JOIN tblCustomers AS c WHERE c.Inactive <> 0;

The query will delete all orders for inactive customers but will not delete the data about those inactive customers.

To achieve similar objective using standard syntax, which does not allow joining on a delete query, would be:

SQL
DELETE FROM tblOrders
WHERE EXISTS (
SELECT NULL
FROM tblCustomers
WHERE Inactive <> 0 AND CustomerID = tblOrders.CustomerID
);

Append Queries


There are two distinct syntaxes: a single record append query and a multiple record append query.

An example of a single record append query would be:

SQL
INSERT INTO Tablename (Field1, Field2)
VALUES (Value1, Value2)

An example of a multiple record append query would be:

SQL
INSERT INTO TargetTablename ("Field1, field2")
SELECT FieldA, FieldB FROM SourceTablename

Note that the SELECT portion of the multiple record append query can be any valid SQL statement.

Update Queries


SQL
UPDATE Tablename SET Field1=Value1, Field2=Value2 WHERE criteria


Make Table Queries


SQL
SELECT Tablename.Field1, Tablename.Field2, Tablename.Field3 INTO Newtable
FROM Tablename;

Calling Methods

RunSQL vs Execute

Edit Discussion
Custom Search
Thank you for your support!
This page has been accessed 12,619 times.  This page was last modified 22:59, 18 January 2015 by djsteele. Contributions by BananaRepublic, Glenn Lloyd and Jack Leach  Disclaimers