I have a table with 606 rows. The first column is simply
ID (starting at 1 to 606), the next column names are
other2, etc. until
I'm trying to create a query that would:
update each cell if column_name like "other" if 0< cellValue < 50 then cellValue=cellValue*10 if 50<=cellValue<120 then cellValue=cellValue*5 if 120<=cellValue<230 then cellValue=cellValue*2
I don't care much about the efficiency because it is a one-shot query.
I worked on it to try to solve it. I got a complicated pseudo-code idea to explain better what I'm trying to do, but I guess it's not needed in SQL because SQL queries check every single value:
begin for i in 1 to 100 for each row if current column is named like "other"i then if 10000< value then value=value/40 elseif 0< value<50 then value=value*10 elseif 50<=value<120 then value=value*5 elseif 120<=value<230 then value=value*2 elseif 230<=value<500 then value=value*2 endif endif end for end for end
I asked some people and they don't know either.
The result of:
SHOW CREATE TABLE example CREATE TABLE `example` ( `id` int(11) NOT NULL, `name1` int(11) NOT NULL, `other1` int(11) NOT NULL, `name2` int(11) NOT NULL, `other2` int(11) NOT NULL, -- -- 2 * 97 columns skipped -- `name100` int(11) NOT NULL, `other100` int(11) NOT NULL ) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci
MySQL Workbench Community (GPL) for Windows version 6.3.5 CE build 201 (64 bit)
It is a local instance of MySQL 5.7
Cairo Version: 1.10.2
OS: Microsoft Windows 8.1