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:

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
  end for
end for

I asked some people and they don't know either.

The result of:


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


2 Answers 2


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 <=  0 THEN other1    -- no change
                  WHEN other1 <  50 THEN other1 * 10
                  WHEN other1 < 120 THEN other1 *  5
                  WHEN other1 < 230 THEN other1 *  2
                  -- possibly more WHEN..THEN clauses 
                  ELSE other1
    other2 = CASE WHEN other2 <=  0 THEN other2       -- no change
                  WHEN other2 <  50 THEN other2 * 10
                  WHEN other2 < 120 THEN other2 *  5
                  WHEN other2 < 230 THEN other2 *  2 
                  ELSE other2
    --- repeat similarly for all columns that need updating
  • Does it mean I have to write 100 times the whole "other" CASE and modify the number so they become "other3", "other4", [...] "other100" ? That's 1 of the 2 things I thought we could avoid. Well, as it's a one-shot and it's "only" 100 columns, I guess I'll do the effort. Jan 11, 2017 at 11:14
  • It would be better if we worked this out in a chat room. Join this: chat.stackexchange.com/rooms/51594/… Jan 11, 2017 at 13:17

While the case statement itself is fairly trivial, you can save yourself some time writing out the hundred UPDATE statements by using something like the following query:

        SET '+COLUMN_NAME+' = CASE 
                        WHEN '+COLUMN_NAME+' <  50 THEN '+COLUMN_NAME+' * 10
                        WHEN '+COLUMN_NAME+' < 120 THEN '+COLUMN_NAME+' *  5
                        WHEN '+COLUMN_NAME+' < 230 THEN '+COLUMN_NAME+' *  2                              
                        ELSE '+COLUMN_NAME+'
WHERE   TABLE_SCHEMA = 'tableSchemaHere'
        AND TABLE_NAME = 'tableNameHere'
        AND COLUMN_NAME LIKE 'other%'

By filling in your Schema and Table names in the WHERE clause the above query will generate a series of a hundred SQL statements to update your columns. Just copy and paste the results into your final script.

You can get it all into one UPDATE statement if you really wanted, but it would require a bit more string manipulation and I am unfortunately not as familiar with MySQL as some other SQL implementations.

  • So, the result is a single column named "'UPDATE'+'TABLE_SCHEMA+'.'+TABLE_NAME+' with 100 rows, each value is "0". I'm afraid I've done something wrong again. Jan 11, 2017 at 15:37
  • Somehow it's treating that all as one string and using it an ALIAS for the column, I'm not sure where the 0 value is coming from . Make sure you're closing all the strings properly?
    – Duffy
    Jan 11, 2017 at 15:45
  • I'm sorry, that's 25 times I read carefully that query, and I don't get how it works. I understand the final goal you said, and it would be good. I just don't get how that query does that. From what I see, the strings are, in this order : "UPDATE ", ".", "SET ", " = CASE WHEN ", etc. I guess the "+" is the concatenator char for strings, but I have no idea what this big SELECT followed by such a string would do. Jan 11, 2017 at 15:54
  • SQL has system level tables that track what makes up the other tables in the DB, the Information Schema puts it in a nice readable format. By querying those tables you can get information about a specific table, the query above is using the Columns table to create an UPDATE string for each row from the target table that's name contains the word 'other'. To do that you concatenate the return columns with some strings to dynamically create the SQL you will copy and run separately.
    – Duffy
    Jan 11, 2017 at 16:09

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.