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

Neeraj Sharma

@neeraj-iAaNcG • Oct 15, 2024

Oct 15, 2024

1.1K

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

Like 0 Replies 18

Replies

Welcome, guest

Join CrazyEngineers to reply, ask questions, and participate in conversations.

CrazyEngineers powered by Jatra Community Platform

Saandeep Sreerambatla

@saandeep-sreerambatla-hWHU1M • 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!!
0
Dancer_Engineer

@dancer-engineer-EJ8rGI • 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. "".

0
K!r@nS!ngu

@kr-at-nsngu-XqzcfU • 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.

0
Dancer_Engineer

@dancer-engineer-EJ8rGI • 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?

0
PraveenKumar Purushothaman

@praveenkumar-66Ze92 • 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... 😀
0
Neeraj Sharma

@neeraj-iAaNcG • Mar 21, 2012

But you are using delete query in it right??
0
PraveenKumar Purushothaman

@praveenkumar-66Ze92 • 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... 😁
0
Saandeep Sreerambatla

@saandeep-sreerambatla-hWHU1M • 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!!
0
PraveenKumar Purushothaman

@praveenkumar-66Ze92 • 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!!! 😀
0

Anoop Kumar

@anoop-kumar-GDGRCn • 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')

PraveenKumar Purushothaman

@praveenkumar-66Ze92 • 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???
0
PraveenKumar Purushothaman

@praveenkumar-66Ze92 • Mar 21, 2012

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

@anoop-kumar-GDGRCn • 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.
0
Anoop Kumar

@anoop-kumar-GDGRCn • Mar 21, 2012

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

@praveenkumar-66Ze92 • 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...
0
Prashant Munshi

@prashant-munshi-qp7Wu5 • 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).
0
PraveenKumar Purushothaman

@praveenkumar-66Ze92 • 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?
0
Prashant Munshi

@prashant-munshi-qp7Wu5 • 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.
0