|
|
Posted by Chris on 09/30/37 11:58
Bob Stearns wrote:
> Chris wrote:
>
>> Hello,
>>
>> I have a table that contains the following records.
>>
>>
+-----+-----+-----------+---------------------------------+------------+----------+
>> | fid | nid | filename | filepath | filemime |
>> filesize |
>>
+-----+-----+-----------+---------------------------------+------------+----------+
>> | 1 | 1 | _original | images/IMG_3002.JPG | image/jpeg |
>> 1930295 |
>> | 2 | 1 | thumbnail | images/IMG_3002.thumbnail.JPG | image/jpeg |
>> 1854 |
>> | 3 | 1 | preview | images/IMG_3002.preview.JPG | image/jpeg |
>> 21818 |
>> | 4 | 2 | _original | images/IMG_3002_0.JPG | image/jpeg |
>> 1930295 |
>> | 5 | 2 | thumbnail | images/IMG_3002_0.thumbnail.JPG | image/jpeg |
>> 1854 |
>> | 6 | 2 | preview | images/IMG_3002_0.preview.JPG | image/jpeg |
>> 21818 |
>>
+-----+-----+-----------+---------------------------------+------------+----------+
>>
>> What I need to do, is change all the rows where the filepath column has a
>> value that contains the characters _0.
>>
>> I've selected these rows by doing:
>>
>> select * from files where filepath like '%_0.%';
>>
>> although I'm sure that there will be a better query to do the same.
>> However I have no idea how I could change the values for that column.
>>
>> SO what I need at the end is:
>>
>>
+-----+-----+-----------+---------------------------------+------------+----------+
>> | fid | nid | filename | filepath | filemime |
>> filesize |
>>
+-----+-----+-----------+---------------------------------+------------+----------+
>> | 1 | 1 | _original | images/IMG_3002.JPG | image/jpeg |
>> 1930295 |
>> | 2 | 1 | thumbnail | images/IMG_3002.thumbnail.JPG | image/jpeg |
>> 1854 |
>> | 3 | 1 | preview | images/IMG_3002.preview.JPG | image/jpeg |
>> 21818 |
>> | 4 | 2 | _original | images/IMG_3002.JPG | image/jpeg |
>> 1930295 | ^^^
>> | 5 | 2 | thumbnail | images/IMG_3002.thumbnail.JPG | image/jpeg |
>> 1854 | ^^^
>> | 6 | 2 | preview | images/IMG_3002.preview.JPG | image/jpeg |
>> 21818 | ^^^
>>
+-----+-----+-----------+---------------------------------+------------+----------+
>>
>>
>> Thanks,
>>
>> Chris
> I don't use MySQL, but in DB2 it would be as shown below. Untested.
>
> UPDATE files
> SET filepath=SUBSTR(filepath,1,POSSTR(filepath,'_O.')-1) ||
> SUBSTR(filepath,POSSTR(filepath,'_O.')+3)
> WHERE POSSTR(filepath,'_O.')>0
Thank you for pointing me in the right direction. It turns out that it can
be done in Mysql like this:
mysql> update files set filepath=replace(filepath,'_0','');
Thanks again,
Chris
Navigation:
[Reply to this message]
|