CODEWITHSUNDEEP
mysqlsql-scripts
user2299251
user2299251

Reputation: 1

Mysql sql script

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

Upvotes: 0

Views: 375

Answers (2)

user2299169
user2299169

Reputation:

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!

Upvotes: 0

Nanne
Nanne

Reputation: 64399

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?

Upvotes: 1

Related Questions