Currently I have a database with the following parameters and entry:

Now I want to fill the column status_from in this particular entry with the id 1 with a substring of the action column. Which is basically I want to fill my status_from column with a word that comes after from and before to of the action column. To achieve this I've tried using the following statement:

INSERT INTO crm_logs2(status_from) SELECT status_from WHERE id='1' VALUES (substring_index(substring_index(action, 'from', -1),'to', 1))

But doing this gave me the following error:

#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'VALUES (substring_index(substring_index(action, 'from', -1), ...' at line 1

CodePudding user response：

You need to use update not insert because this record is already exists

but in case of searching inside a text, you could use like

UPDATE crm_logs2 SET status_form = now() WHERE action like '%website%'

Note: change website with the word that you want to search with but don't forget to add it between % %

CodePudding user response：

You need to use the UPDATE statement

UPDATE crm_logs2 
SET status_from = (SELECT SUBSTRING_INDEX(SUBSTRING_INDEX(action, "from", -1), "to", 1) FROM crm_logs2 WHERE id = 1)
WHERE id = 1