How do I turn this Select statement into a Delete Statement?

Question

How do I turn this Select statement into a Delete Statement?

Mike Feuti

Old Hand

Points: 303
More actions
February 11, 2008 at 1:22 pm

#186498

I have the below Select statment and it pulls the records I want to delete from our activity table. How do I turn this select statement into a delete statement? Thank you in advance.
select name.ID,activity_type,log_text from name,activity,name_log
where name.id=activity.id and activity.id=name_log.id
and activity_type like '%ibad%'
and name_log.log_text like '%email%'
and name_log.date_time>activity.transaction_date
and log_text like '%@%>%@%'
and name.email<>activity.description
group by name.id,activity_type,log_text

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Matt Miller (4) SSC Guru Points: 124210 More actions · Answer 1

Keeping in mind that you are doing group by in your select (so there are duplicate records being hidden). The fact that you're using the SAME fields in both joins scares me a little as well.

That being said - it looks like the "equivalent" delete statement would be:

delete activity

from name,activity,name_log

where name.id=activity.id and activity.id=name_log.id

and activity_type like '%ibad%'

and name_log.log_text like '%email%'

and name_log.date_time>activity.transaction_date

and log_text like '%@%>%@%'

and name.email<>activity.description

You REALLY ought to switch the join syntax over to ansi-compatible syntax. That could have a big impact on determining what may or may no get picked up by this query.

----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?

Mike Feuti Old Hand Points: 303 More actions · Answer 2

I appreciate your help and obviously I'm fairly new at this. I'll have to look more into what you said about ansi-compatable syntax. And why would using the SAME fields in both joins be a bad thing? Sorry for my ingorance.

Thank you,

Matt Miller (4) SSC Guru Points: 124210 More actions · Answer 3

I'm not sure that it is. Some of the less intelligent SQL editors will link things blindly based on fields called the same thing, instead of making sure it really IS related. Of course - I think the relations are probably both based off of the same table, and not two separate parent-child relations on the same field, which might not be quite so odd looking.

that being said - it's not entirely unheard of. I just usually make SURE that's the way it should be, since you're getting ready to delete some data....

----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?

Vijaya Kadiyala SSCrazy Eights Points: 9119 More actions · Answer 4

Hi,

It would be better if you store this SQL query results into Temprary table and use the table to delete the records from activity table.

Thanks -- Vj

http://dotnetvj.blogspot.com

Thanks -- Vijaya Kadiyala
www.dotnetvj.com
SQL Server Articles For Beginers

Jeff Moden SSC Guru Points: 1000539 More actions · Answer 5

That's normally the way I do it, too.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
"Change is inevitable... change for the better is not".

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Intro to Tally Tables and Functions