0

We often need to do following, retrieve product ids for a user and then delete them from the database. 

select id from mytable where user=123;

and then we delete those id returned from query above:

delete from mytable where id in (1, 5, 6, 7);

It is possible that while select is returning data from server to client, a new record for that user is inserted and hence we are selectively deleting ids.

Is there a better way to achieve it?

Thanks

Edit: some of you have answered, delete from mytable where user=123;

but it's a wrong answer as a new row might be inserted between mysql server completed the query and mysql client yet to receive response from the server.

mesibo
  • 3,970
  • 6
  • 25
  • 43
  • What if the application cannot complete its processing? Your method seems reasonable. – Gordon Linoff Apr 11 '16 at 12:17
  • https://stackoverflow.com/questions/652770/delete-with-join-in-mysql – vp_arth Apr 11 '16 at 12:19
  • 3
    what don't just do `delete from mytable where user=123`? – mb14 Apr 11 '16 at 12:20
  • If both query is related to the same table, why can't you use "delete from mytable where user=123;"? – Sanjay Kumar N S Apr 11 '16 at 12:21
  • What means `new record for that user is inserted`? – vp_arth Apr 11 '16 at 12:24
  • When you want to delete SOME rows from user 123, but not "new" ones, you have a state that depends on a race condition rather than data. Are the "ids" nullable? Do you only want specific rows from user XY / 123 to be deleted? – Daniel W. Apr 11 '16 at 12:30
  • Do you have a timestamp on the record? if so that could be used to control which ones you delete.This is probably the easiest. Another approach is to have a 'state' column on the record. Always set to new on insert then updated later. Including specifically marking it for deletion? – Ryan Vincent Apr 11 '16 at 13:02
  • @mb14 It's a wrong answer - pls read my updates. – mesibo Apr 12 '16 at 03:56
  • @RyanVincent and how do you make the state white select. It's a same problem again. timestamp likely to work 99% but no guarantee that two items won't be having the same time stamp. – mesibo Apr 12 '16 at 03:58
  • i think you need to better explain your problem with more details – Xsmael Nov 26 '19 at 22:16

0 Answers0