List:General Discussion« Previous MessageNext Message »
From:Larry Martell Date:February 13 2014 2:37am
Subject:Re: Conditional in update
View as plain text  
On Wed, Feb 12, 2014 at 5:35 PM,  <hsv@stripped> 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.
Thread
Conditional in updateLarry Martell11 Feb 2014
  • Re: Conditional in updatehsv13 Feb 2014
    • Re: Conditional in updateLarry Martell13 Feb 2014