October 16, 2010 at 2:08 am
Hai guys,
Is it possible, Delete a record without using Delete keyword?
October 16, 2010 at 2:47 am
October 16, 2010 at 3:48 am
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
October 16, 2010 at 3:54 am
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
October 16, 2010 at 3:55 am
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
October 16, 2010 at 4:00 am
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
October 16, 2010 at 4:45 am
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....
October 16, 2010 at 5:37 am
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" 😉
October 16, 2010 at 7:47 am
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?
October 16, 2010 at 8:47 am
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.
October 17, 2010 at 4:09 am
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.
October 19, 2010 at 3:23 pm
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.
October 19, 2010 at 3:28 pm
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.
October 19, 2010 at 3:32 pm
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... 😉
October 19, 2010 at 3:42 pm
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.
Viewing 15 posts - 1 through 15 (of 20 total)
You must be logged in to reply to this topic. Login to reply