DELETE syntax - jus checking in..

Question

DELETE syntax - jus checking in..

celticpiping

Old Hand

Points: 386
More actions
October 23, 2015 at 8:58 am

#304444

The data is play data, so not a big deal, but I just ran this delete query, and
it deleted ALL of the table data:
delete shipping_data
where order_no=123456789
there was only 1 row that matched that order_no
order_no data type is 'char'
is that why?
Would I have needed quotes in order to limit it to just one value?
Rich

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

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

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 1

If there's only one order with that value, the delete would have deleted one row. SQL would have implicitly converted the string column to int to do the comparison.

Did you perhaps highlight just the first line and run it? Very common mistake, I suspect most people have done it at least once

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Jayanth_Kurup SSC-Insane Points: 22967 More actions · Answer 2

no way delete statement will delete all rows unless they matched the filter criteria, most likely its what Gail said , select first line and press F5. Happens to everybody , If you are concerned about it try using an is deleted flag instead of an actual delete.

Jayanth Kurup[/url]

celticpiping Old Hand Points: 386 More actions · Answer 3

huh, very interesting..

well, I suppose then, if SQL rules dictate that it could NOT

have deleted everything, then I MUST have selected the top line.

But I think when I'm back in the office, I just might re-run a trial

and verify...it bugs me, because I 'swear' I hadn't highlighted anything,

and hit F5...

thanks fellas for the replies,

Rich

Jeff Moden SSC Guru Points: 1003863 More actions · Answer 4

celticpiping (10/25/2015)
huh, very interesting..
well, I suppose then, if SQL rules dictate that it could NOT
have deleted everything, then I MUST have selected the top line.
But I think when I'm back in the office, I just might re-run a trial
and verify...it bugs me, because I 'swear' I hadn't highlighted anything,
and hit F5...
thanks fellas for the replies,
Rich

Try the exact same experiment as before. Make sure you have the same indexes and that you run it on the same machine as before. [font="Arial Black"]The reason why I'm so interested in this is because it HAS happened before.[/font] I believe it was post 2000 SP2 but SQL Server ignored a WHERE clause that one fellow wrote (the DBA witnessed it happening) and it caused 40 people to scramble for more than a week trying to recover the data (the backups had also been failing for a month an no one knew. Fortunately, I wasn't the DBA).

It was later that MS came out with a hotfix for the problem (had to do with parallelism and a bunch of other things that gathered in a perfect storm).

And note that the same code ran just fine on another box on identical data because it had fewer CPUs and the level of parallelism didn't occur as it had on the production box. We were able to duplicate the problem on an identical server as the production box.

I'm hoping that MS hasn't somehow resurrected that problem.

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

celticpiping Old Hand Points: 386 More actions · Answer 5

wow, very interesting..!

I'll surely let you know what I find

R

Kim Crosser SSCommitted Points: 1763 More actions · Answer 6

Jeff Moden (10/25/2015)

[font="Arial Black"]The reason why I'm so interested in this is because it HAS happened before.[/font] I believe it was post 2000 SP2 but SQL Server ignored a WHERE clause that one fellow wrote (the DBA witnessed it happening) and it caused 40 people to scramble for more than a week trying to recover the data (the backups had also been failing for a month an no one knew. I was "just" a Senior Developer back then).

Yep - "impossible" things do happen.

I was working a contract in New Zealand on an Oracle system and was getting "impossible" results in some complex queries. After many hours, I was finally able to reduce it to a trivial case:

select ... blah ... from table

-- returned 2 records

select ... blah ... from table where <condition>

-- returned 3 records ??????

Tain't possible, McGee - adding a Where clause returns MORE records than without the Where clause?

I worked my way through three levels of Oracle's 24-hour global help desks, with each one patiently explaining to me that they would have to charge me for this call. And then, as each one understood what I was telling them, it became "Oh <expletive>" and I got bumped up another level.

2 days later - a new release. It turned out that their query optimizer had a bug such that in certain cases, adding the where condition caused the query to include previously deleted records from the table.

And no - they didn't charge me for the call... 😀

Jeff Moden SSC Guru Points: 1003863 More actions · Answer 7

Kim Crosser (10/26/2015)

Jeff Moden (10/25/2015)
[font="Arial Black"]The reason why I'm so interested in this is because it HAS happened before.[/font] I believe it was post 2000 SP2 but SQL Server ignored a WHERE clause that one fellow wrote (the DBA witnessed it happening) and it caused 40 people to scramble for more than a week trying to recover the data (the backups had also been failing for a month an no one knew. I was "just" a Senior Developer back then).
Yep - "impossible" things do happen.
I was working a contract in New Zealand on an Oracle system and was getting "impossible" results in some complex queries. After many hours, I was finally able to reduce it to a trivial case:
select ... blah ... from table
-- returned 2 records
select ... blah ... from table where <condition>
-- returned 3 records ??????
Tain't possible, McGee - adding a Where clause returns MORE records than without the Where clause?
I worked my way through three levels of Oracle's 24-hour global help desks, with each one patiently explaining to me that they would have to charge me for this call. And then, as each one understood what I was telling them, it became "Oh <expletive>" and I got bumped up another level.
2 days later - a new release. It turned out that their query optimizer had a bug such that in certain cases, adding the where condition caused the query to include previously deleted records from the table.
And no - they didn't charge me for the call... 😀

I'll just bet they didn't send you a check for finding such a problem either. 😀

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

Kim Crosser SSCommitted Points: 1763 More actions · Answer 8

Jeff Moden (10/26/2015)
I'll just bet they didn't send you a check for finding such a problem either. 😀

True that... 🙁

celticpiping Old Hand Points: 386 More actions · Answer 9

hmm

well, I must have highlighted the top line...

because I re-ran, and deleted 1 row with:

delete shipping_data

where order_no='1000002'

and running;

delete shipping_data

where order_no=1000002

produced an error: "Conversion failed when converting the varchar value"

which makes sense..it IS a char type after all...

thanks fellers..

Jayanth_Kurup SSC-Insane Points: 22967 More actions · Answer 10

actually in the second case there will be an implicit conversion which can be demonstrated with the below code.

create table #tmp

( id int ,

data varchar(10)

)

insert into #tmp

select 1 , '01'

union

select 2 , '1'

go

delete from #tmp

where data =1

--notice how two rows got deleted the column value get converted into int

-- confirm this by looking at the execution plan

drop table #tmp

Your order number probably has alpha numeric data in it which caused the conversion error.

Jayanth Kurup[/url]

Jeff Moden SSC Guru Points: 1003863 More actions · Answer 11

celticpiping (10/26/2015)
hmm
well, I must have highlighted the top line...
because I re-ran, and deleted 1 row with:
delete shipping_data
where order_no='1000002'
and running;
delete shipping_data
where order_no=1000002
produced an error: "Conversion failed when converting the varchar value"
which makes sense..it IS a char type after all...
thanks fellers..

NP. Thank you for figuring out what you did because it means that it's not an MS bug that you came across.

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