Updating whole table once, criteria : column_name and each value 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&lt; cellValue &lt; 50 then cellValue=cellValue*10
if 50&lt;=cellValue&lt;120 then cellValue=cellValue*5
if 120&lt;=cellValue&lt;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&lt; value then value=value/40
      elseif 0&lt; value&lt;50 then value=value*10
      elseif 50&lt;=value&lt;120 then value=value*5
      elseif 120&lt;=value&lt;230 then value=value*2
      elseif 230&lt;=value&lt;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
This is a very simple query. You can use a CASE expression, one for each column that needs updating:

UPDATE table_name
SET other1 = CASE WHEN other1 &lt;=  0 THEN other1    -- no change
                  WHEN other1 &lt;  50 THEN other1 * 10
                  WHEN other1 &lt; 120 THEN other1 *  5
                  WHEN other1 &lt; 230 THEN other1 *  2
                  -- possibly more WHEN..THEN clauses 
                  ELSE other1
             END,
    other2 = CASE WHEN other2 &lt;=  0 THEN other2       -- no change
                  WHEN other2 &lt;  50 THEN other2 * 10
                  WHEN other2 &lt; 120 THEN other2 *  5
                  WHEN other2 &lt; 230 THEN other2 *  2 
                  ELSE other2
             END,
    --- repeat similarly for all columns that need updating
  ;