Advanced Delete Statement 1

[jaredmcguire]

I have a database with 2 tables EmailList and Optout. The EmailList table contains 3 fields: FirstName, LastName, and EmailAddress. The Optout table has one field: EmailAddress.

I have figured out how to perform a SELECT query that will display records from the EmailList table if there email is also in the Optout table.

My question is how do I turn into a DELETE query? Below is what I have so far and it gives me an error that simply says it can't delete the data. I'm testing this in access and when I run this as a view it returns the data to delete with no problem. What am I doing wrong?

DELETE EmailList.*
FROM EmailList INNER JOIN optout ON EmailList.EmailAddress = optout.EmailAddress
WHERE EmailList.EmailAddress=optout.EmailAddress

 

[swampBoogie]

A delete statement is used to delete from one table only. It is possible to define that a delete in one table will cause a delete in a related table. This is done when defining the relationship between the tables.

create table emailList(FirstName varchar(30), LastName varchar(30), EmailAddress varchar(200) primary key)

create table optOut(EmailAddress varchar(200) references emailList(EmailAddress) on delete cascade)

if you do a delete on the emailList table that will delete related records in the optout table as well.

delete from emailList where emailAddress in (select emailAddress from optout



If it is not feasible to set it up this way you need to do two delete statements

delete from emailList where emailAddress in (select emailAddress from optout where someCondition)

delete from optout where someCondition

 

[tlbroadbent]

Which RDMS are you using and what is the exact message you get when you use the DELETE you posted. Terry L. Broadbent - DBA
SQL Server Page:

http://tlbroadbent.home.attbi.com/sql/sql_articles.htm

If you want to get the best answer for your question read faq183-874.

 

[jaredmcguire]

swampBoogie: I am only trying to delete from the EmailList table. The Optout table is what I am using for criteria. I want it to delete any records from the EmailList teble that have a matching EmailAddress field in the Optout table. I know I can do this as a SELECT statement.

tlbroadbent: I don't know what you mean by RDMS. I am resting this with an access database on my desktop, so every thing is local at the moment. the exact error I get is a popup in access taht says "Could not delete from specified tables".

 

[swampBoogie]

This would delete records from emaillist where there is an matching entry in the optOut table

delete from emailList where emailAddress in (select emailAddress from optout)


RDBMS = Relational DataBase Management System

 

[jaredmcguire]

thanks swampBoogie, that worked wonderfully.

 

[tlbroadbent]

RDMS: Relational Database Management System. In your case, that would be MS Access. May I suggest that you post Access SQL questions in forum701. Access JET SQL is not strictly ANSI compliant so you'll get better answers, faster in the correct forum. Terry L. Broadbent - DBA
SQL Server Page:

http://tlbroadbent.home.attbi.com/sql/sql_articles.htm

If you want to get the best answer for your question read faq183-874.