Help with delete command

Question

Post reply

Help with delete command

Mike Gray-474150

SSC Eights!

Points: 997
More actions
October 23, 2008 at 2:33 pm

#194895

I'm trying to do some cleanup in our Uptime database and I need a little help with the delete syntax.
Here is the script I wrote to delete entrys old than 2008-07-01.
delete
from performance_esx3_workload
where exists
(
select * from performance_sample ps, performance_esx3_workload pp
where pp.sample_id = ps.id and ps.sample_time < '2008-07-01'
);
When I run
select count (*)
from performance_sample ps, performance_esx3_workload pp
where pp.sample_id = ps.id and ps.sample_time < '2008-07-01'
I get 1,975,693 returned but when I run the following command I get 2,618,303 basically all the rows in the performance_esx3_workload table.
begin transaction
delete
from performance_esx3_workload
where exists
(
select * from performance_sample ps, performance_esx3_workload pp
where pp.sample_id = ps.id and ps.sample_time < '2008-07-01'
);
any ideas why I get two different results?
Mike

Viewing 13 posts - 1 through 12 (of 12 total)

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

Jerry Hung SSChampion Points: 12968 More actions · Answer 1

In SQL, you can do DELETE FROM too

see how many count(1) you get?

DELETE PW -- COUNT(1)

FROM performance_esx3_workload PW

INNER JOIN performance_sample PS

ON PW.sample_id = PS.id

AND PS.sample_time <= '2008-07-01'

SQLServerNewbieMCITP: Database Administrator SQL Server 2005

Mike Gray-474150 SSC Eights! Points: 997 More actions · Answer 2

I get a syntax error

Incorrect syntax near the keyword 'WHERE'

any ideas?

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 3

Lynn Pettis

SSC Guru

Points: 442467

October 23, 2008 at 3:28 pm

#888911

Post your code you wrote.

😎

Mike Gray-474150 SSC Eights! Points: 997 More actions · Answer 4

I ran the code that Jerry posted

DELETE PW -- COUNT(1)

FROM performance_esx3_workload PW

INNER JOIN performance_sample PS

WHERE PW.sample_id = PS.id

AND PS.sample_time < '2008-07-01'

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 5

Try this in a test environment:

DELETE PW -- COUNT(1)

FROM

performance_esx3_workload PW

INNER JOIN performance_sample PS

on (PW.sample_id = PS.id)

WHERE

PS.sample_time < '2008-07-01'

😎

Jerry Hung SSChampion Points: 12968 More actions · Answer 6

sorry my bad

Replace the WHERE in my code with ON, that should do it

SQLServerNewbieMCITP: Database Administrator SQL Server 2005

Mike Gray-474150 SSC Eights! Points: 997 More actions · Answer 7

Mike Gray-474150

SSC Eights!

Points: 997

October 23, 2008 at 3:51 pm

#888929

Sweet!! that worked. Thanks guys!

Jeff Moden SSC Guru Points: 1004372 More actions · Answer 8

Mike Gray (10/23/2008)
I ran the code that Jerry posted
DELETE PW -- COUNT(1)
FROM performance_esx3_workload PW
INNER JOIN performance_sample PS
WHERE PW.sample_id = PS.id
AND PS.sample_time < '2008-07-01'

You didn't use Jerry's code... you changed it. "Must Look Eye". 😉

--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)

Garadin One Orange Chip Points: 29613 More actions · Answer 9

Actually, he probably did. Jerry edited his post 😉 I make a habit of looking for that, as a lot of times people answer questions as edits to their original posts.

Seth Phelabaum

Consistency is only a virtue if you're not a screwup. 😉

Links: How to Post Sample Data[/url] :: Running Totals[/url] :: Tally Table[/url] :: Cross Tabs/Pivots[/url] :: String Concatenation[/url]

Jeff Moden SSC Guru Points: 1004372 More actions · Answer 10

Dang... you're right... there is an edit on Jerry's post. Thanks for the reminder, Seth. You'd think folks would at least admit to making a correction, but it would be better if they just opened another post with the correction.

--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)

Jerry Hung SSChampion Points: 12968 More actions · Answer 11

?? I never said I never edited my post

I also said I made an error on the INNER JOIN clause (replace WHERE with ON)

I edited the original post so that future people won't copy-and-paste the BAD code

It's all good, I am more surprised the OP didn't figure out the obvious syntax error himself/herself before we can reply 😛

SQLServerNewbieMCITP: Database Administrator SQL Server 2005

Garadin One Orange Chip Points: 29613 More actions · Answer 12

I edited the original post so that future people won't copy-and-paste the BAD code

I do the same. I wasn't trying to say you did anything wrong... in fact, I think you edited in nearly the perfect manner; you edited the original, and then made a note below that you edited it.

While I think it sometimes makes people look like they're not paying attention (I'll see a post and ask a question, only to have the OP edit the original with the answer and make it appear that I just didn't read it), I think that it's probably helpful to the people who come in after the fact who can get the facts of the situation by reading the original request, rather than having to sort through 10 posts worth of Q&A.

Seth Phelabaum

Consistency is only a virtue if you're not a screwup. 😉

Links: How to Post Sample Data[/url] :: Running Totals[/url] :: Tally Table[/url] :: Cross Tabs/Pivots[/url] :: String Concatenation[/url]