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

Neeraj Sharma · 2012-03-18T06:30:57+00:00

This was my interview question today. Can anybody answer it?

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

Neeraj Sharma
@neeraj-iAaNcG

Updated: Oct 15, 2024

Views: 1.0K

This was my interview question today. Can anybody answer it?

0

Replies

Howdy guest!

Dear guest, you must be logged-in to participate on CrazyEngineers. We would love to have you as a member of our community. Consider creating an account or login.

Replies

Saandeep Sreerambatla

Member • Mar 18, 2012

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

Are you sure? This action cannot be undone.
Cancel
Dancer_Engineer

Member • Mar 21, 2012

#-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. "".

Are you sure? This action cannot be undone.
Cancel
K!r@nS!ngu

Member • Mar 21, 2012

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.

Are you sure? This action cannot be undone.
Cancel
Dancer_Engineer

Member • Mar 21, 2012

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?

Are you sure? This action cannot be undone.
Cancel
PraveenKumar Purushothaman

Member • Mar 21, 2012
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... 😀
Are you sure? This action cannot be undone.
Cancel
Neeraj Sharma

Member • Mar 21, 2012

But you are using delete query in it right??

Are you sure? This action cannot be undone.
Cancel
PraveenKumar Purushothaman

Member • Mar 21, 2012

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

Are you sure? This action cannot be undone.
Cancel
Saandeep Sreerambatla

Member • Mar 21, 2012

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

Are you sure? This action cannot be undone.
Cancel
PraveenKumar Purushothaman

Member • Mar 21, 2012

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

Are you sure? This action cannot be undone.
Cancel
Anoop Kumar

Member • Mar 21, 2012
@#-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') 
```
Are you sure? This action cannot be undone.
Cancel
PraveenKumar Purushothaman

Member • Mar 21, 2012
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???
Are you sure? This action cannot be undone.
Cancel
PraveenKumar Purushothaman

Member • Mar 21, 2012

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

Are you sure? This action cannot be undone.
Cancel
Anoop Kumar

Member • Mar 21, 2012

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.

Are you sure? This action cannot be undone.
Cancel
Anoop Kumar

Member • Mar 21, 2012

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

Are you sure? This action cannot be undone.
Cancel
PraveenKumar Purushothaman

Member • Mar 21, 2012

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

Are you sure? This action cannot be undone.
Cancel
Prashant Munshi

Member • Apr 30, 2012

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

Are you sure? This action cannot be undone.
Cancel
PraveenKumar Purushothaman

Member • Apr 30, 2012

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?

Are you sure? This action cannot be undone.
Cancel
Prashant Munshi

Member • Apr 30, 2012

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.

Are you sure? This action cannot be undone.
Cancel