Edit a query in SQL view to perform multiple actions

[dandc]

Hi,

How can I edit a query in SQL view to run multiple lines of SQL?

For example I can use the GUI to create a query, then in SQL view enter the following:

DELETE
FROM Employees_copy;

which clears the Employees_copy table. Perfect. But what if I want to do this for mulitple tables in the same query eg for a second table Employees_copy_2 as well? I've tried


DELETE
FROM Employees_copy
DELETE
FROM Employees_copy_2;

which gives a syntax error, as does

DELETE
FROM Employees_copy,
DELETE
FROM Employees_copy_2;

any suggestions?

 

[The_Doc_Man]

This violates a basic concept of SQL.

DELETE FROM xxxxxx WHERE yyyy=zzzz ; is OK because SQL works that way. Ditto DELETE FROM xxxxxxx; (no WHERE clause). In either case, the DELETE verb specifies the action (remove records). The FROM clause specifies the place. (The PLACE, SINGULAR!) The WHERE clause provides selectivity, but you can omit it if you don't want selectivity.

To do this to multiple tables at once, you violate the assumption that SQL can work sequentially on a single data source.

You can try to do something CLOSE to this a few different ways.

1. Copy the query, then edit it to include the second table name. Rename it to reference the second table name in its displayed query title. Run the queries from a Macro RunSQL action that has multiple lines.

2. Make the query a parameter query for which the table name is the parameter. Run the query with different parameters each time.

3. If the tables have at least one field in common (and it looks like your case would have that), do an OUTER JOIN of the two tables across that common field and then write a DELETE query through the joined query - with no WHERE clause in the DELETE query.

 

[dandc]

OK thanks for the tip Doc Man

OK I thought that might be the case, I'm having to readjust my mindset a bit from my days with SQL Server.

cheers

dandc