CODEWITHSUNDEEP
mysql
MatKus
MatKus

Reputation: 74

update column set longest value from all values in another column

I have table "reports" like this:

key | symbol | name   | price | longest_name
1   | a1     | abc    | 1     | NULL
2   | a2     | qwe    | 2     | NULL 
3   | a1     | a      | 5     | NULL
4   | a2     | xyz1   | 4     | something
5   | a3     | zz     | 1     | NULL
6   | a1     | qweasd | 0     | NULL

i want in column "longest_name" longest value from column "name", but only from rows with same symbol and only, if "longest_name" IS NULL

so, with this data, result will look like this:

key | symbol | name   | price | longest_name
1   | a1     | abc    | 1     | qweasd
2   | a2     | qwe12  | 2     | qwe12
3   | a1     | a      | 5     | qweasd
4   | a2     | xyz    | 4     | something
5   | a3     | zz     | 1     | zz
6   | a1     | qweasd | 0     | qweasd

I'm trying different approaches but with no luck, mainly because i think i can't modify table and read it in same time.

Any ideas how to solve this with one query?

Honestly, i'm not sure if MySQL will do this for me, because table have few milion rows, so there may be memory problems, but still worth to try. If nothing else, i'll write PHP script, that will do this row by row, but it will take ages to complete and i will have to do this again after some time.

Upvotes: 0

Views: 44

Answers (1)

Darshan Mehta
Darshan Mehta

Reputation: 30839

You can write an update query like this:

UPDATE test t
SET t.longest_name = (
    SELECT a.name 
    FROM (
       SELECT symbol, name
       FROM test t1
       WHERE LENGTH(name) = (
         SELECT MAX(LENGTH(NAME))
         FROM test
         WHERE symbol = t1.symbol
       )
    ) a
    WHERE a.symbol = t.symbol
)
WHERE longest_name IS NULL;

The logic here is to use one more level of nesting to get around the MySQL error of updating and selecting from the same table.

Here's the SQL Fiddle.

Upvotes: 1

Related Questions