|
|
Posted by Bob Stearns on 10/14/88 11:58
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
[Back to original message]
|