Looking for the right mysql syntax
am 17.09.2006 00:21:54 von Krimgelas
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
Re: Looking for the right mysql syntax
am 17.09.2006 05:08:08 von Robert Stearns
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
Re: Looking for the right mysql syntax
am 17.09.2006 13:58:57 von Krimgelas
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