On Wed, Feb 12, 2014 at 5:35 PM, <h...@tbbs.net> wrote: >>>>> 2014/02/11 18:14 -0500, Larry Martell >>>> > set LIMIT = sign(LIMIT) * 100 * floor(0.000001 + (sign(LIMIT) * LIMIT > * ratio/100) > <<<<<<<< > The function TRUNCATE can be useful here: > set LIMIT = TRUNCATE(LIMIT * ratio + 0.000001 * sign(LIMIT), -2) > , if it works as advertized. In any case, > ABS(LIMIT) = sign(LIMIT) * LIMIT > . > > As for limiting the value, see this (clipping can be useful to you): > > > 11.2.6. Out-of-Range and Overflow Handling > > When MySQL stores a value in a numeric column that is outside the permissible > range of the column data type, the result depends on the SQL mode in effect > at the time: > > * If strict SQL mode is enabled, MySQL rejects the out-of-range value > with an error, and the insert fails, in accordance with the SQL standard. > * If no restrictive modes are enabled, MySQL clips the value to the > appropriate endpoint of the range and stores the resulting value instead. > When an out-of-range value is assigned to an integer column, MySQL stores the > value representing the corresponding endpoint of the column data type range. > If you store 256 into a > <file:///C:/PROGRA%7E1/MySQL/MYSQLD%7E1.14/HTML/data-types.html#integer-types>TINYINT > or TINYINT UNSIGNED column, MySQL stores 127 or 255, respectively. > When a floating-point or fixed-point column is assigned a value that > exceeds the range implied by the specified (or default) precision and scale, > MySQL stores the value representing the corresponding endpoint of that range. > > Column-assignment conversions that occur due to clipping when MySQL is not > operating in strict mode are reported as warnings for > <file:///C:/PROGRA%7E1/MySQL/MYSQLD%7E1.14/HTML/sql-syntax.html#alter-table>ALTER > TABLE, > <file:///C:/PROGRA%7E1/MySQL/MYSQLD%7E1.14/HTML/sql-syntax.html#load-data>LOAD > DATA INFILE, > <file:///C:/PROGRA%7E1/MySQL/MYSQLD%7E1.14/HTML/sql-syntax.html#update>UPDATE, > and multiple-row > <file:///C:/PROGRA%7E1/MySQL/MYSQLD%7E1.14/HTML/sql-syntax.html#insert>INSERT > statements. In strict mode, these statements fail, and some or all the values > will not be inserted or changed, depending on whether the table is a > transactional table and other factors. For details, see > <file:///C:/PROGRA%7E1/MySQL/MYSQLD%7E1.14/HTML/server-administration.html#server-sql-mode>Section > 5.1.7, “Server SQL Modes”.
Thanks for the reply. I was able to do this with a case statement, but then the requirements were changed and I had to know when I constrained the limit so I could log it to a file. So I ended up just doing the update as it was originally, then adding a select after to find any rows that exceeded the limit, and then updating those to the max or min, and then I could log them to a file. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql
