Re: Conditional in update
On Wed, Feb 12, 2014 at 5:35 PM, wrote: > 2014/02/11 18:14 -0500, Larry Martell > set LIMIT = sign(LIMIT) * 100 * floor(0.01 + (sign(LIMIT) * LIMIT > * ratio/100) > > The function TRUNCATE can be useful here: > set LIMIT = TRUNCATE(LIMIT * ratio + 0.01 * 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 > 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 > ALTER > TABLE, > LOAD > DATA INFILE, > UPDATE, > and multiple-row > 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 > 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
Re: Conditional in update
2014/02/11 18:14 -0500, Larry Martell set LIMIT = sign(LIMIT) * 100 * floor(0.01 + (sign(LIMIT) * LIMIT * ratio/100) The function TRUNCATE can be useful here: set LIMIT = TRUNCATE(LIMIT * ratio + 0.01 * 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 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 ALTER TABLE, LOAD DATA INFILE, UPDATE, and multiple-row 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 Section 5.1.7, Server SQL Modes. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Conditional in update
Is there some way I can have a conditional in an update?
I have this update sql (paraphrased):
update LMPROC_LIMITS
set LIMIT = sign(LIMIT) * 100 * floor(0.01 + (sign(LIMIT) * LIMIT
* ratio/100)
where SYMBOL_ID = symbolId
and CLASSTYPE = LimitType
and TYPE_ in ('minClusterPosition', 'maxClusterPosition',
'minProductPosition', 'maxProductPosition', 'minBookPosition',
'maxBookPosition', 'maxShortShares')
(ratio, symbolId, and LimitType all are filled in at run time)
What i want to do is limit (no pun intended) the value LIMIT is set to
to +/-2,147,483,647 - i.e. if the result of (sign(LIMIT) * 100 *
floor(0.01 + (sign(LIMIT) * LIMIT * ratio/100)) is more or less
then that, I want to set it to +/-2,147,483,647 Can I do this in SQL?
Thanks!
-larry
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql
