I have a table which has a few varchar fields named like this:
我有一个表有几个varchar字段,如下所示:
image_fullres | image_highres | image_mediumres | image_lowres | image_thumbnail
image_fullres | image_highres | image_mediumres | image_lowres | image_thumbnail
I currently have the same value for each of these fields(differing by row), so image_fullres has the same path as image_lowres and so on.
我目前每个字段的值都相同(按行不同),因此image_fullres与image_lowres具有相同的路径,依此类推。
an example value for these paths would be http://mysite.com/images/image1.jpg
这些路径的示例值是http://mysite.com/images/image1.jpg
I would like to have each of these fields to contain a different and appropriate path name, so:
我想让每个字段包含一个不同的和适当的路径名,所以:
- image_fullres has value of http://mysite.com/images/fullres/image1.jpg
- image_highres has value of http://mysite.com/images/highres/image1.jpg
- image_mediumres has value of http://mysite.com/images/mediumres/image1.jpg
image_fullres的值为http://mysite.com/images/fullres/image1.jpg
image_highres的值为http://mysite.com/images/highres/image1.jpg
image_mediumres的值为http://mysite.com/images/mediumres/image1.jpg
and so on...
等等...
Since all of my data is already in the database with duplicate paths by row, what SQL can I execute that would change the value of each field's data to an appropriate path?
由于我的所有数据都已经在具有重复路径的数据库中,我可以执行哪些SQL将每个字段的数据值更改为适当的路径?
1 个解决方案
#1
12
Example:
UPDATE table
SET image_fullres = REPLACE(image_fullres, 'http://mysite.com/images/', 'http://mysite.com/images/fullres/')
This will update the image_fullres column for all rows in your table. You can do one such statement for each column, or combine them into one:
这将更新表中所有行的image_fullres列。您可以为每列执行一个此类语句,或将它们合并为一个:
UPDATE table
SET
image_fullres = REPLACE(image_fullres, 'http://mysite.com/images/', 'http://mysite.com/images/fullres/'),
image_highres = REPLACE(image_highres, 'http://mysite.com/images/', 'http://mysite.com/images/highres/'),
...
...
#1
12
Example:
UPDATE table
SET image_fullres = REPLACE(image_fullres, 'http://mysite.com/images/', 'http://mysite.com/images/fullres/')
This will update the image_fullres column for all rows in your table. You can do one such statement for each column, or combine them into one:
这将更新表中所有行的image_fullres列。您可以为每列执行一个此类语句,或将它们合并为一个:
UPDATE table
SET
image_fullres = REPLACE(image_fullres, 'http://mysite.com/images/', 'http://mysite.com/images/fullres/'),
image_highres = REPLACE(image_highres, 'http://mysite.com/images/', 'http://mysite.com/images/highres/'),
...
...