In the table, rows are generated initially (a list of PIN codes) and then only two fields are updated: status status (from 0 to 1 and back) and order number field ord_id PIN codes with certain IDs (a series in succession) in one order I do this:

UPDATE pin_codes SET status=1, ord_id=223344 WHERE status=0 AND (id BETWEEN 300 AND 345)

If all the requested PIN codes have already been sold (status = 1), then the update will not happen, everything is ok.
If all the requested PIN codes are free, then everything is OK too - an update will occur as expected.
Question : how to disable the update, if a part of the requested interval of PIN codes has already been sold, that is, in fact, only part of the lines will be updated.
The point is that the sale is by series, but different customers choose different series, for someone the series from 300 to 345 is suitable, for someone from 300 to 400, for someone from 320 to 350 and so on and always completeness series.
Crazy thought to first make a select and then immediately threw it out on the results of the update
That is, if simplified, then allow to make an update only when all the conditions are met

  • That is, if it is oversimplified, then you are allowed to make an update only if all the conditions are fully met - Sergey V.

1 answer 1

Use a subquery to check:

 UPDATE pin_codes SET status=1, ord_id=223344 WHERE not exists (select 1 from (select * from pin_codes where (id between 300 and 345) and status=1 ) a ) AND (id BETWEEN 300 AND 345)

http://sqlfiddle.com/#!9/ee4d9/1
UPD. In MySql you need a little crutches, corrected the answer.

  • What will happen if id 300 status is modified from 0 to 1. Then the request will be taken to modify the 301st. What kind of subquery will see the 300th? Old 0 or new 1? After all, if there is 1, then such a method modifies exactly one record and this will all end. - Sergey
  • Modifies everything. Fiddle added - Zufir
  • I read somewhere that before the update, MySQL gets all the necessary information, so how can I get it too without an extra subquery and put it into processing? and also - are temporary collisions possible in your version, that is, when several users contact almost simultaneously? - Sergey V.
  • ok, it works! The request for 1 million lines is performed 0.1665 seconds, not bad, but there is much to move. I think, if I have MyISAM, then the table will be completely blocked for this whole request and there will be no temporary collisions a priori? - Sergey V.