Delete record without using Delete keyword

  • Hai guys,

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

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

    Delete from table

    where bah = bah

  • 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!

  • 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

  • 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
  • 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!

  • 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]

  • 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" 😉

  • 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?

  • 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]

  • 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.

  • 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.

  • 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]

  • 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]

  • 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