Can you lock updates which will change too many rows in a database?

Question

Question by Abe Petrillo

Is there a way of implementing a rule in mysql which will block updates effecting over 10 rows?

For instance, lets say i have tbl1 with 50,000 rows and I do

update tbl1 set tb1.name = 'bob'

Then quickly follow on with a “OH NO! I FORGOT THE WHERE!?!?”.

The reason I ask is because a junior developer made this mistake and now its repair time :(.

Answer 1

You can prevent update (and delete) queries without where clause with this query

set sql_safe_updates=1;

Take a look at this link

i-am-a-dummy is the correct switch for your colleague. 🙂

Answer 2

Use LIMIT

Example

update tbl1 set tb1.name = 'bob' LIMIT 0,10

This will only update the first 10 rows;