Multiple Delete Queries

[ooch1]

Hello,

I have got four delete queries that basically delete everything out of 4 tables using the * method.

Can someone let me know how i can combine the four into one as i am hoping to use similar rules on the four append queries as well, since the number of queries i now have is getting out of control.

OOch

 

[Crowley16]

erm...

have you tried:

delete * from tbl1, tbl2, tbl3...

--------------------
Procrastinate Now!

 

[ooch1]

I have now.....i just got an error message asking me to specify which table i want to delete the records from.

The code is as follows:

DELETE *
FROM Gas_Aston_PortAnlys_IMPORT,Gas_Hull_PortAnlys_IMPORT,Gas_Oldbury_PortAnlys_IMPORT,Gas_Sol_PortAnlys_IMPORT;

OOch

 

[Crowley16]

Oh, Oops...

then maybe:

DELETE tbl1.*, tbl2.*... FROM tbl1, tbl2...

--------------------
Procrastinate Now!

 

[ooch1]

Crowley16

I'm afraid there is still no joy. It now gives me the message unable to delete from the tables, which should not happen because when i run them individually they all run fine.

My code is now:

DELETE Gas_Aston_PortAnlys_IMPORT.*, Gas_Hull_PortAnlys_IMPORT.*, Gas_Oldbury_PortAnlys_IMPORT.*, Gas_Sol_PortAnlys_IMPORT.*
FROM Gas_Aston_PortAnlys_IMPORT, Gas_Hull_PortAnlys_IMPORT, Gas_Oldbury_PortAnlys_IMPORT, Gas_Sol_PortAnlys_IMPORT;

OOch

 

[PHV]

I'm afraid that to delete the records from 4 tables you have to run 4 delete queries.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[ooch1]

PHV

Thanks for the confirmation. Do these rules also apply for append queries??

OOCh

 

[PHV]

I think so: only ONE table for INSERT INTO.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[ooch1]

PHV
When you say only one table for each insert into, then does that imply that if you are inserting into 1 table using 4 different queries then this could be consolidated?

OOch

 

[PHV]

The syntax is:
INSERT INTO oneTable (fieldlist) SELECT fieldlist FROM any tables/queries you can join

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[ooch1]

PHV - Think this is where i get a little confused, as i'm not to hot on sub queries.

For example, if i wanted to select a [customer ID] from both
[Internet cusomers] and [telephone customers]which are mutually exclusive tables, then how would i combine this into one select query??

OOch

 

[PHV]

No, two append queries, as I don't think an append query admits an union query.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[lespaul]

There was a thread the other day that had a UNION in an APPEND. I'm pretty sure the syntax was like this:

INSERT INTO tblName (Field1, Field2) 
   SELECT Field1, Field2 FROM 
     (SELECT SomeField As Field1, AnotherFIeld As Field 2 FROM OtherTable WHERE Something = "X" 
      UNION 
      SELECT SomeField, AnotherFIeld FROM ADifferentTable WHERE SomethingElse = "Y")

Leslie