Write a UPDATE query to DELETE a record from a table?

Replies

• 

  Saandeep Sreerambatla

  You can do this may be it works,
  
  consider you have 10 rows and you have to delete 8th row.
  
  Then using update query , update the content of 8th row with 9th and 9th with 10th.
  THen update 10th row with null values 😀
  
  This is the answer I would have given!
  
  I think there will be a better answer as well!!
• 

  Dancer_Engineer

  #-Link-Snipped-#, the solution may not be feasible if I have to delete the 2nd row from a table having 100 rows.
  
  Updating the 2nd row with NULL will do the required job.
  
  Another solution is to update the row to be deleted with 'zero-length string', i.e. "".
• 

  K!r@nS!ngu

  Dancer_Engineer

  #-Link-Snipped-#, the solution may not be feasible if I have to delete the 2nd row from a table having 100 rows.
  
  Updating the 2nd row with NULL will do the required job.

  Updating the data with Null values will not permanently delete the row/data.
  

  Dancer_Engineer

  #-Link-Snipped-#
  Another solution is to update the row to be deleted with 'zero-length string', i.e. "".

  "Zero" will be considered in this case.
  
  There could be more feasible answer 😐 ??.
  
  seems to be the toughest question.
• 

  Dancer_Engineer

  K!r@nS!ngu

  seems to be the toughest question.

  Yeah. Could be a tricky one too.
  
  Could the answer be as 'not possible to permanently delete record using update query'? 😁
  
  #-Link-Snipped-#, do you have any solution?
• 

  PraveenKumar Purushothaman

  Hi, Thanks #-Link-Snipped-#! Yes, one possible way is to use the UNION operator...
  
  Try this:

  UPDATE `table` SET `abc` = `cde` WHERE 1 = 0 UNION DELETE FROM `table` WHERE `condition` = `value`;

  This works for sure... 😀
• 

  Neeraj Sharma

  But you are using delete query in it right??
• 

  PraveenKumar Purushothaman

  Nick_Sharma

  But you are using delete query in it right??

  But its an update query, as they haven't said anything about using other queries... It should be a single update query... That's it... 😁
• 

  Saandeep Sreerambatla

  Great Praveen! when I answered it I didnt think of the null record. my solution will delete only values not the row!!
• 

  PraveenKumar Purushothaman

  English-Scared

  Great Praveen! when I answered it I didnt think of the null record. my solution will delete only values not the row!!

  Thanks ES!!! 😀
• 

  Anoop Kumar

  @#-Link-Snipped-# : As I checked your solution is not working.
  Here is using merge statement.
  its working.
  

    Merge INTO  table123 t1
    using (select * from  table123 where ID1 ='DEL_IT') t2
    ON (t1.ID1 = t2.ID1)
    WHEN  Matched then
    update set t1.ID2='YourValue'
    Delete where (t1.ID1 ='DEL_IT') 

• 

  PraveenKumar Purushothaman

  ianoop

  @#-Link-Snipped-# : As I checked your solution is not working.
  Here is using merge statement.
  its working.
  

    Merge INTO  table123 t1
    using (select * from  table123 where ID1 ='DEL_IT') t2
    ON (t1.ID1 = t2.ID1)
    WHEN  Matched then
    update set t1.ID2='YourValue'
    Delete where (t1.ID1 ='DEL_IT') 

  Thankz but is this a single query???
• 

  PraveenKumar Purushothaman

  Still it is not an UPDATE query... Its a MERGE query!!! 😔 My query needs to be checked! 😔
• 

  Anoop Kumar

  Yes its single query using merge..
  first it is update the same row and then deleting it. So it is a update query.
  Simply, merge satatment is used to update a table from a source.
• 

  Anoop Kumar

  sorry can't able to add semicolon in above post.giving some error
• 

  PraveenKumar Purushothaman

  ianoop

  sorry can't able to add semicolon in above post.giving some error

  No that's what... Its a MERGE query and not UPDATE query...
• 

  Prashant Munshi

  This answer probably is much late. But those answers which have been given here before are none a workable solution, for sure. I would need to know how and in what context this question was asked. I believe there is no straight forward answer to this question or rather there may be no answer, the interviewer may be just playing a prank. SQL is not known to posses any such syntax u could delete a row using update verb (command).
• 

  PraveenKumar Purushothaman

  Prashant Munshi

  This answer probably is much late. But those answers which have been given here before are none a workable solution, for sure. I would need to know how and in what context this question was asked. I believe there is no straight forward answer to this question or rather there may be no answer, the interviewer may be just playing a prank. SQL is not known to posses any such syntax u could delete a row using update verb (command).

  So, is there a solution for it?
• 

  Prashant Munshi

  In my opinion there is none, the question is nothing but a prank. But every person is limited by his / her experience and knowledge he / she has, so my comments be taken with due modesty. I shall still keep on doing research however. But for now I have no option but to reject the question.