Delete where exists query - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Delete where exists query

Author

Topic

masterdineen
Aged Yak Warrior

550 Posts

Posted - 2010-09-15 : 08:51:27

Hello everyone. I am using the below statement,

Delete from email

where email_address exists

(select newlist.Individual_Ref, DBList.email_address, newlist.email_address from BIFM.dbo.email as DBList
inner join BIFM_LEADS.dbo.email as newlist
on NewList.email_address = DBList.email_address)

but i am recieving an error saying the syntax near 'exists'
is incorrect.

Can anyone let me know where i am going wrong.

Kind Regards

Rob

MCTS / MCITP certified

Sachin.Nand

2937 Posts

Posted - 2010-09-15 : 08:55:08

quote:
Originally posted by masterdineen

Hello everyone. I am using the below statement,

Delete from email

where ~~email_address~~ exists

(select newlist.Individual_Ref, DBList.email_address, newlist.email_address from BIFM.dbo.email as DBList
inner join BIFM_LEADS.dbo.email as newlist
on NewList.email_address = DBList.email_address)

but i am recieving an error saying the syntax near 'exists'
is incorrect.

Can anyone let me know where i am going wrong.

Kind Regards

Rob

MCTS / MCITP certified

Limitations live only in our minds. But if we use our imaginations, our possibilities become limitless.

PBUH

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-09-15 : 08:55:50

WHERE EXISTS(select * from ...)

But be sure to have a relation between your delete statement and the query inside the EXISTS().

No, you're never too old to Yak'n'Roll if you're too young to die.

masterdineen
Aged Yak Warrior

550 Posts

Posted - 2010-09-15 : 08:59:45

ok thank you.

when i run the subquery on its own, it returns 14 rows that match. But when i tie it in with

Delete from email

where exists

(select newlist.Individual_Ref, DBList.email_address, newlist.email_address from BIFM.dbo.email as DBList
inner join BIFM_LEADS.dbo.email as newlist
on NewList.email_address = DBList.email_address)

it deletes everything from the table.

why is this

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-09-15 : 09:02:20

Because there is no relation between the delete and the exists().
Please give table structure, sample data and wanted result.

No, you're never too old to Yak'n'Roll if you're too young to die.

masterdineen
Aged Yak Warrior

550 Posts

Posted - 2010-09-15 : 10:10:04

below is the table structure of my email table. I also have another small table with email addresses that i want to compare to
the table below. I want to delete emails from my small table that are already in the main table.

table structure.

[dbo].[EMAIL](
[EMAIL_REF] [int] NOT NULL,
[INDIVIDUAL_REF] [int] NULL,
[ORGANISATION_REF] [int] NULL,
[TYPE] [int] NOT NULL,
[MAIN_EMAIL] [char](1) NULL,
[EMAIL_ADDRESS] [varchar](200) NULL,
[COMMENTS] [text] NULL,
[VALID_FROM] [datetime] NULL,
[VALID_TO] [datetime] NULL,
[CREATE_TIMESTAMP] [datetime] NULL

sample data

1 1 NULL 1302 Y email.ac.uk NULL NULL NULL NULL NULL NULL NULL
2 2 NULL 1302 Y email.ac.uk NULL NULL NULL NULL NULL NULL NULL
3 3 NULL 1302 Y email.ac.uk NULL NULL NULL NULL NULL NULL NULL
4 4 NULL 1302 Y email.ac.uk NULL NULL NULL NULL NULL NULL NULL

rohitvishwakarma
Posting Yak Master

232 Posts

Posted - 2010-09-15 : 10:24:56

How come the query posted is going to delete some records when the stattement EXISTS is always going to return true? It will always delete all the rows as EXISTS is True for all the rows:

If I execute the following query:

SELECT * FROM BIFM.dbo.email
WHERE EXISTS
(select newlist.Individual_Ref, DBList.email_address, newlist.email_address from BIFM.dbo.email as DBList
inner join BIFM_LEADS.dbo.email as newlist
on NewList.email_address = DBList.email_address)

It will return all the rows in BIFM.dbo.email rather than whatever you are getting from the subquery ie. 14.

Hence it is going to delete all the rows in the BIFM.dbo.email table.

masterdineen
Aged Yak Warrior

550 Posts

Posted - 2010-09-15 : 10:31:28

I have already tried this, but it deletes everything from the main email table.

rohitvishwakarma
Posting Yak Master

232 Posts

Posted - 2010-09-15 : 10:38:02

India
54 Posts
Posted - 09/15/2010 : 10:24:56
--------------------------------------------------------------------------------

How come the query posted is going to delete some records when the stattement EXISTS is always going to return true? It will always delete all the rows as EXISTS is True for all the rows:

If I execute the following query:

SELECT * FROM BIFM.dbo.email
WHERE EXISTS
(select newlist.Individual_Ref, DBList.email_address, newlist.email_address from BIFM.dbo.email as DBList
inner join BIFM_LEADS.dbo.email as newlist
on NewList.email_address = DBList.email_address)

It will return all the rows in BIFM.dbo.email rather than whatever you are getting from the subquery ie. 14.

Hence it is going to delete all the rows in the BIFM.dbo.email table

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-09-15 : 10:38:39

Your table named email in your delete - is it BIFM.dbo.email or is it BIFM_LEADS.dbo.email or is it a third table?

No, you're never too old to Yak'n'Roll if you're too young to die.

rohitvishwakarma
Posting Yak Master

232 Posts

Posted - 2010-09-15 : 10:41:19

Better to get rid of EXISTS and use:
DELETE FROM BIFM.dbo.email
WHERE email_address IN
(select DBList.email_address from BIFM.dbo.email as DBList
inner join BIFM_LEADS.dbo.email as newlist
on NewList.email_address = DBList.email_address)

X002548
Not Just a Number

15586 Posts

Posted - 2010-09-15 : 10:49:25

...and WHY is it better?

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

rohitvishwakarma
Posting Yak Master

232 Posts

Posted - 2010-09-15 : 10:52:44

quote:
Originally posted by X002548

...and WHY is it better?

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

As it is pretty much clear about the data that is going to be deleted. I have no idea about the background process taking place in case of IN / EXISTS. Maybe you can throw some more light on the topic. Read somewhere that EXISTS is better as compared to IN from effeciency point of view.

rohitvishwakarma
Posting Yak Master

232 Posts

Posted - 2010-09-15 : 10:56:54

@X002548

SELECT * FROM BIFM.dbo.email
WHERE EXISTS
(select newlist.Individual_Ref, DBList.email_address, newlist.email_address from BIFM.dbo.email as DBList
inner join BIFM_LEADS.dbo.email as newlist
on NewList.email_address = DBList.email_address)

The above query returns all the rows in the table BIFM.dbo.email however, the subquery is returning 14 rows. As long as Subquery is returning a minimum of 1 row the EXISTS part is true and hence all the records are going to be affected

masterdineen
Aged Yak Warrior

550 Posts

Posted - 2010-09-15 : 10:57:10

well done rohitvishwakarma

i used

DELETE FROM BIFM_LEADS.dbo.email -- real DB
WHERE email_address IN
(select DBList.email_address from BIFM.dbo.email as DBList
inner join BIFM_LEADS.dbo.email as newlist
on NewList.email_address = DBList.email_address)

and it worked, thank you very much.

rohitvishwakarma
Posting Yak Master

232 Posts

Posted - 2010-09-15 : 11:20:03

@masterdineen
If you want to use EXISTS then use the following query:

DELETE T1
FROM BIFM_LEADS.dbo.email AS T1
WHERE EXISTS(SELECT * FROM BIFM.dbo.email AS T2 WHERE T1.email_address=T2.email_address)

masterdineen
Aged Yak Warrior

550 Posts

Posted - 2010-09-15 : 11:39:11

well done, that works well too.

Thank you very much for your help.

rohitvishwakarma
Posting Yak Master

232 Posts

Posted - 2010-09-15 : 11:43:05