Re: DELETE with LIMIT (or my first hack)

On Wed, Dec 1, 2010 at 4:01 AM, Daniel Loureiro <loureirorg@gmail.com> wrote:
> A) an feature MySQL-like which will DELETE/UPDATE just K tuples
> B) an feature to protect the database in case the DBA forget the "WHERE"
> statement
>

MySQL has B as well. To quote the manual:
"For beginners, a useful startup option is --safe-updates (or
--i-am-a-dummy, which has the same effect). This option was introduced
in MySQL 3.23.11. It is helpful for cases when you might have issued a
DELETE FROM tbl_name statement but forgotten the WHERE clause.
Normally, such a statement deletes all rows from the table. With
--safe-updates, you can delete rows only by specifying the key values
that identify them. This helps prevent accidents.
...   *      You are not permitted to execute an UPDATE or DELETE
statement unless you specify a key constraint in the WHERE clause or
provide a LIMIT clause (or both). For example:
     UPDATE tbl_name SET not_key_column=val WHERE key_column=val;
     UPDATE tbl_name SET not_key_column=val LIMIT 1;
   *      The server limits all large SELECT results to 1,000 rows
unless the statement includes a LIMIT clause.   *      The server aborts multiple-table SELECT statements that
probably need to examine more than 1,000,000 row combinations."

I have actually suggested that a certain subset of my users only
connect to the database if they are willing to use the --i-am-a-dummy
flag.


-- 
Rob Wultsch
wultsch@gmail.com