How to match text in one column in phpmyadmin and replace the value in another?

Question

MySQL

The main question is in the title. What SQL query to submit to solve such a problem?
There is a table dle_post , it has two necessary columns xfields and date . We need to find the text in the xfields column and if it matches, change the value in the date column .
(search and replace in the entire table)

Is it generally possible to implement this with queries?

Answer 1

Is it even possible to implement this with requests?

Easy :)
If we assume that the "year" parameter cannot be the first in the parameter set and its length is always 4, then this:

UPDATE  
  `dle_post`
SET
  `date` = CONCAT(
    LEFT(SUBSTR(`xfields`, LOCATE('||year|', `xfields`)+7), 4),
    RIGHT(`date`, 15)
  )
WHERE 
  LOCATE('||year|', `xfields`) > 0

Refined requirements:

the date does not need to be calculated, but simply indicate your own - for all news that has the year 1997.
The match of the text with the search was not correctly expressed.
If it finds such a year (the value, in this example, 1997) in the xfields column, then we change it to an arbitrary date (specified manually) in the date column

Decision:

UPDATE  
  `dle_post`
SET
  `date` = CONCAT(
    '2014-03-24', # подставьте тут нужную дату
    RIGHT(`date`, 9)
  )
WHERE xfields LIKE '%year|1997%' # подставьте тут желаемый год