From: Larry Martell
Date: February 13 2014 2:37am
Subject: Re: Conditional in update
List-Archive: http://lists.mysql.com/mysql/230102
Message-Id:
MIME-Version: 1.0
Content-Type: text/plain; charset=UTF-8
Content-Transfer-Encoding: quoted-printable
On Wed, Feb 12, 2014 at 5:35 PM, wrote:
>>>>> 2014/02/11 18:14 -0500, Larry Martell >>>>
> set LIMIT =3D sign(LIMIT) * 100 * floor(0.000001 + (sign(LIMIT) * LIMIT
> * ratio/100)
> <<<<<<<<
> The function TRUNCATE can be useful here:
> set LIMIT =3D TRUNCATE(LIMIT * ratio + 0.000001 * sign(LIMIT), -2)
> , if it works as advertized. In any case,
> ABS(LIMIT) =3D 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 permiss=
ible range of the column data type, the result depends on the SQL mode in e=
ffect 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 a=
ppropriate endpoint of the range and stores the resulting value instead. Wh=
en an out-of-range value is assigned to an integer column, MySQL stores the=
value representing the corresponding endpoint of the column data type rang=
e. If you store 256 into a TINYINT or TINYINT UNSIGNED column, MySQL sto=
res 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 ra=
nge.
>
> Column-assignment conversions that occur due to clipping when MySQL is no=
t operating in strict mode are reported as warnings for ALTER TABLE, LOAD D=
ATA INFILE, UPDATE, and multiple-row INSERT statements. In strict mode, these state=
ments fail, and some or all the values will not be inserted or changed, dep=
ending on whether the table is a transactional table and other factors. For=
details, see Section 5.1.7, =E2=80=9CServer SQL Modes=E2=
=80=9D.
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.