Tomax Tomax - 1 month ago 7
MySQL Question

Update all rows of a specific table using a calculated substring of each field it self

After some search around I've found some answers witch I used and try to solve my problem, but with no success so far.

I have a table with a column called company_details. Each field have a different string content, but all of them have a portion between 2 words somewhere. I want to find this portion of the text and update the correspondent field with the correspondent found text .

From some other answer of the forum I've manage to get the column the text between "this" and "that" like the example I built:

SELECT SUBSTRING(
sentence,
LOCATE('this', sentence),
LOCATE('that', sentence) - LOCATE('this', sentence))
FROM (SELECT "Hello this is a test, will finish that. For some reason..." AS sentence) test;


The result (witch select "Hello this is a test, will finish that. For some reason...") is: 'this is a test, will finish '.

So far so good, now I want to go through all the table and get the field value, select the substring and update the field to the new value.

So I'm trying to use this query:

UPDATE compdata
SET company_details=(SELECT SUBSTRING(
sentence,
LOCATE('this', sentence),
LOCATE('that', sentence) - LOCATE('this', sentence))
FROM (SELECT company_details AS sentence from compdata) teste);


I'm getting the error:


"Error Code: 1242. Subquery returns more than 1 row",


witch I can understand why, having multiple results on the Select Substring.
(I can swear Yesterday this query run and worked fine).
I now this is possible with a LOOP or something, but I'm stuck, any solution will be appreciated, since should be run like 1 tine a year performance is not an issue that big and there are only 59K rows on the table.

Answer

You could use a simple update without the subselect

  UPDATE compdata
  SET company_details=  SUBSTRING(
      company_details,
      LOCATE('this', company_details),
      LOCATE('that', company_details) - LOCATE('this', company_details))