0

I have a table T with a few columns out of which 1 column is having comma separated values. I want to process this column and update for all rows in the following manner

the column contains values like "15,20"; "30,15,50" (not in sorted order). And there are only 10 distinct possible values

Processing logic

Split the column based on delimeter (,) Find the max value in the resulting list. Based on max value, update the column with a specific value.

I am new to Mysql scripting. So, it would be helpful if you can give some pointers of how this can be solved

user2299251
  • 1
  • Is there a good reason to 1. have a comma separated value list in a column (hint: there aren't many!) and 2. you need to be doing this in sql? – Nanne Apr 24 '13 at 11:04
  • UNfortunately, thats how the db has been designed and yes i need to wirte a sql script – user2299251 Apr 24 '13 at 11:06
  • so you cannot redesign the database? An why do you need to do this in sql? Performance? – Nanne Apr 24 '13 at 11:06
  • Cannot re-design the db. – user2299251 Apr 24 '13 at 11:11
  • And the other question? why does it have to be sql? – Nanne Apr 24 '13 at 11:14
  • Lets say, I am not the one making this decision. Obviously, writing a perl/python script would have been much much easier – user2299251 Apr 24 '13 at 11:20
  • Not neccessairily a seperate script, but since I assume this sql magic is going to be called in some environment (aka: your app), some of this business-logic might be better suited in that app. There is absolutely no influence to have in the process? that is .. .unfortunate.... – Nanne Apr 24 '13 at 11:22

2 Answers2

1

Warning

Apart from the fact that I am convinced that this is not the way to do it, there is a way. But I cannot stress enough that the database-design is a problem, and the descision to do this logic in the sql as well. This should not be.

Please, don't do this. Having said that...

Hacking

You need this, I think to understand from your question:

  1. Split the string
  2. Find the highest value
  3. Do a query based on that.

The first is the hardest. See for instance this question about exploding and the mysql manual for string functions (especially the comments). It can be done, but you need to do some trickery, and using your knowledge about the limits on your string (the amount, the way they are formed etc) you can probably do something hackish using SUBSTRING_INDEX like in those examples.

If you have the separate values, you can compare them to find the highest one. You might be able to use the sql MAX function, but it might be quicker to do comparisons when you're doing all the substring-split-explode hacking anyway.

Now you have a value, you can either use some sort of CASE statement to hardcode effects, or enter it in a query.

Preaching

The database design looks wrong, but it could be a valid reason. There could also be limits on what you can change (e.g. it is not your database), so a given is a given. Sad but true.

The fact that you need to do this in sql, I'm having a harder time with that. There should be someone who has a reason to want this, and that someone is probably wrong. Could you go to this person and figure out what the reason is?

Community
  • 1
  • 1
Nanne
  • 64,065
  • 16
  • 119
  • 163
0

This guy here selects the biggest number from the list (you can run this code from a mySQL UI immediate/query window or command line to test):

SET @foo = CONCAT('SELECT ', REPLACE('40,50,10,20', ',', ' UNION ALL SELECT '));
SET @foo = CONCAT('SELECT * FROM (', @foo, ' ORDER BY 1 DESC) AS uncleFoo LIMIT 1');
PREPARE STMT FROM @foo; 
EXECUTE STMT;
DEALLOCATE PREPARE STMT;

So. The first "SET @foo" is sort of an explode function: It creates an SQL which selects numbers from the list into table rows. In the example above I entered "40,50,10,20" as you can see but you can use any number of items and any delimeter instead of ",".
Comment the 2nd "SET" line and execute query to see what exactly happens.

The next "SET @foo" extends the query so only the largest number will be given back (sort of a "SELECT max([field])" but we can't use 'max' here because of the nature of the query).
Then we execute the query so it returns only the largest number from the list.

If you want to update a field with the result, you should extended the query like:
UPDATE myTable SET myField=[value from above @foo execution] WHERE [your clause]

I think from this point you can easily figure out how to update the field you want in your specific case.
You can also move the above code into a function if it's not a "one time execution" to fix something but you'll need this update later too.

Hope this helps!
  • oh! forgot to mention! the values ('40,50,10,20') and delimeter were added for testing purposes. you can use table values there of course! so it turns into "...REPLACE(SELECT [field] FROM [table]...,". I think it's obvious but I mentioned anyway :) –  Apr 24 '13 at 13:27