Delete record without using Delete keyword

Question

Post reply

Delete record without using Delete keyword

jaffrey

Mr or Mrs. 500

Points: 530
More actions
October 16, 2010 at 2:08 am

#239828

Hai guys,
Is it possible, Delete a record without using Delete keyword?

Viewing 15 posts - 1 through 15 (of 20 total)

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

Scott Ohar Hall of Fame Points: 3607 More actions · Answer 1

http://msdn.microsoft.com/en-us/library/ms189835.aspx

Delete from table

where bah = bah

Lowell SSC Guru Points: 323507 More actions · Answer 2

jaffrey (10/16/2010)
Hai guys,
Is it possible, Delete a record without using Delete keyword?

a single record? no...the TRUNCATE TABLE [TABLENAME] command will delete all records in a table. so that deletes records without that keyword.

is that what you are after?

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

matzer SSCrazy Eights Points: 9727 More actions · Answer 3

Lowell (10/16/2010)
jaffrey (10/16/2010)
Hai guys,
Is it possible, Delete a record without using Delete keyword?
a single record? no...the TRUNCATE TABLE [TABLENAME] command will delete all records in a table. so that deletes records without that keyword.
is that what you are after?

In that case DROP TABLE and DROP DATABASE will work also (but don't try that at work):-D

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

Why on earth are you looking for this? This has all the signs of someone trying to get around a keyword blacklist, maybe SQL injection, maybe code standards, etc.

Why and for what reason?

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

Lowell SSC Guru Points: 323507 More actions · Answer 5

GilaMonster (10/16/2010)
Why on earth are you looking for this? This has all the signs of someone trying to get around a keyword blacklist, maybe SQL injection, maybe code standards, etc.
Why and for what reason?

sounds more like an interview question to me...testing for depth of knowledge?

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

LutzM SSC Guru Points: 107049 More actions · Answer 6

Lowell (10/16/2010)
jaffrey (10/16/2010)
Hai guys,
Is it possible, Delete a record without using Delete keyword?
a single record? no...the TRUNCATE TABLE [TABLENAME] command will delete all records in a table. so that deletes records without that keyword.
is that what you are after?

How about

SELECT col_list

INTO newTable

FROM oldTable

WHERE col_id<>[value_to_be_deleted]

DROP TABLE oldTable

EXEC sp_rename 'newTable', 'oldTable'; :-P:-D

Of course, there will be slightly more effort if foreign keys, constraints, triggers and the like will be involved....

Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question[/url]
How to post performance related questions[/url]
Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

Perry Whittle SSC Guru Points: 233944 More actions · Answer 7

I'm with Gail on this WHY???

Having said that, to delete a row without using DELETE simply requires an UPDATE which nullifies any column data. OK the row isn't actually deleted, but it no longer means anything in the data apsect so it has essentially been removed!!

-----------------------------------------------------------------------------------------------------------

"Ya can't make an omelette without breaking just a few eggs" 😉

jaffrey Mr or Mrs. 500 Points: 530 More actions · Answer 8

Of course, there will be slightly more effort if foreign keys, constraints, triggers and the like will be involved....

So far the query works fine as long as the constraints,triggers,procedures are not used.If it is the case what is the query would be then?

LutzM SSC Guru Points: 107049 More actions · Answer 9

jaffrey (10/16/2010)
Of course, there will be slightly more effort if foreign keys, constraints, triggers and the like will be involved....
So far the query works fine as long as the constraints,triggers,procedures are not used.If it is the case what is the query would be then?

Before going any further I'd like to know the true reason why you don't use the simple DELETE statement. Also, please describe the business case you're working on.

Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question[/url]
How to post performance related questions[/url]
Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

jaffrey Mr or Mrs. 500 Points: 530 More actions · Answer 10

LutzM (10/16/2010)

jaffrey (10/16/2010)

Before going any further I'd like to know the true reason why you don't use the simple DELETE statement. Also, please describe the business case you're working on.

I really am not come into that situation,just i want to know whether its possible or not... Anyhow

First of all i thank you great job for answering to my questions very politely and patiently.

Cldewar Valued Member Points: 51 More actions · Answer 11

How about data disappeared from my table and there are no deletes in any of my procedure. What could have done this?

I encountered a problem where the delete was run against the wrong table. Took forever to find that one.

LutzM SSC Guru Points: 107049 More actions · Answer 12

Cldewar (10/19/2010)
How about data disappeared from my table and there are no deletes in any of my procedure. What could have done this?
I encountered a problem where the delete was run against the wrong table. Took forever to find that one.

Please don't hijack a thread.

If you have a question, open a new thread and ask.

Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question[/url]
How to post performance related questions[/url]
Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

LutzM SSC Guru Points: 107049 More actions · Answer 13

jaffrey (10/17/2010)

LutzM (10/16/2010)

jaffrey (10/16/2010)
Before going any further I'd like to know the true reason why you don't use the simple DELETE statement. Also, please describe the business case you're working on.
I really am not come into that situation,just i want to know whether its possible or not... Anyhow
First of all i thank you great job for answering to my questions very politely and patiently.

To answer your question: yes, it's possible (to some extent).

But at one point it's much easier to remove the root cause for not using the DELETE statement. And this point usually is reached long before the alternatives are consdered... 😉

Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question[/url]
How to post performance related questions[/url]
Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

Cldewar Valued Member Points: 51 More actions · Answer 14

LutzM (10/19/2010)
Cldewar (10/19/2010)
How about data disappeared from my table and there are no deletes in any of my procedure. What could have done this?
I encountered a problem where the delete was run against the wrong table. Took forever to find that one.
Please don't hijack a thread.
If you have a question, open a new thread and ask.

Gail asked why anyone would ask this. This is my suggestion. I do not have any questions.