Posted by Bob Stearns on 06/15/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

• Next in forum: Re: Help with a query please !! It works but needs adjusting !
• Prev in forum: Re: Help with a query please !! It works but needs adjusting !
• Thread view: Re: Looking for the right mysql syntax

[Reply to this message]