2

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

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
             END,
    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
             END,
    --- 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. – NewMilenium Jan 11 '17 at 11:14
  • It would be better if we worked this out in a chat room. Join this: chat.stackexchange.com/rooms/51594/… – ypercubeᵀᴹ Jan 11 '17 at 13:17
1

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:

SELECT  'UPDATE '+TABLE_SCHEMA+'.'+TABLE_NAME+'
        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+'
                     END'
FROM    INFORMATION_SCHEMA.COLUMNS
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. – NewMilenium Jan 11 '17 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 '17 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. – NewMilenium Jan 11 '17 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 '17 at 16:09

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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