List:General Discussion« Previous MessageNext Message »
From:hsv Date:February 12 2014 10:35pm
Subject:Re: Conditional in update
View as plain text  
>>>> 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, 
Thread
Conditional in updateLarry Martell11 Feb 2014
  • Re: Conditional in updatehsv13 Feb 2014
    • Re: Conditional in updateLarry Martell13 Feb 2014