DELETE the contents of a SELECT statement

[Joff01]

Hi,

I'm trying to delete all the records returned in a SELECT statment, but I can't seem to get the syntax right.

I've tried:

DELETE * FROM (SELECT * FROM table_x WHERE field_x = x)

and

DELETE FROM (SELECT * FROM table_x WHERE field_x = x)

But neither seem to do the trick.

If it's of any use, I'm currently using MS Access, with the SQL in ASP (I thought I'd ask here and get the ANSI syntax first, then worry about any MS differences!).

The SELECT by itself returns all the desired records I'm trying to delete.

 

[r937]

you're gonna kick yourself, it's so easy

[tt]delete
from table_x
where field_x = x[/tt]

the thing is, you don't have to "return" them in order to delete them (ASP thinking, if i'm not mistaken)

just tell the database which rows you want deleted


rudy

 

[Joff01]

Ah, ok.

The thing is, the SELECT is using a couple of inner joins to link multiple tables, so I can't specify a table to delete from as there's several.

 

[DaPi]

You'll want to delete from one table at a time (I hope!).

delete from x
where x.f1=something
and exists
(select * from y,z
where x.f2=y.f2 etc)
DaPi - no silver bullet

 

[r937]

if you want to delete based on conditions in other tables, that's fine, however, you must designate exactly one table to delete from

if you also want related rows in other tables to be deleted, then declare ON DELETE CASCADE for the foreign keys and the database engine will take care of it, you won't need to code all the subsidiary deletes yourself

 

[Joff01]

Right, I've sorted it now - thanks for both your help.

You were right, I just wasn't deleting from each table!!

 

[Joff01]

I've used ON DELETE CASCADE before with Oracle, but I'm not even going to see if Access supports it

 

[r937]

"Constraints can also be used to establish referential integrity between database tables. Having referential integrity means that the data is consistent and uncorrupted. For example, if you deleted a customer record but that customer's shipping record remained in the database, the data would be inconsistent because you now have an orphaned record in the shipping table. Referential integrity is established when you build a relationship between tables. In addition to establishing referential integrity, you can also ensure that the records in the referenced tables stay in sync by using cascading updates and deletes. For example, when the cascading updates and deletes are declared, if you delete the customer record, the customer's shipping record is deleted automatically.

"To enable cascading updates and deletions, use the ON UPDATE CASCADE and/or ON DELETE CASCADE keywords in the CONSTRAINT clause of an ALTER TABLE statement. Note that they must be applied to the foreign key.

ALTER TABLE tblShipping
ADD CONSTRAINT FK_tblShipping
FOREIGN KEY (CustomerID) REFERENCES
tblCustomers (CustomerID)
ON UPDATE CASCADE
ON DELETE CASCADE

Intermediate Microsoft Jet SQL for Access 2000

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnacc2k/html/acintsql.asp