I have a table with 606 rows. The first column is simply ID
(starting at 1 to 606), the next column names are name1
, other1
, name2
, other2
, etc. until name100
, other100
.
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
I'm using:
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
Best Answer
This is a very simple query. You can use a
CASE
expression, one for each column that needs updating: