MySQL decrement number within tags

[Aaron]

Hello, on my SMF forum I have posts that are numbers within [embed][/embed] tags, example:

[embed]1[/embed]
[embed]2[/embed]
[embed]3[/embed]
[embed]4[/embed]

I have over 10,000 of these but need to remove some and need to decrement the numbers that are above this amount, such as 9001~9500, so that 9501 then becomes 9001 and 9502 becomes 9002 etc.

These are only in the smf_messages table of course.

Is it possible to do this in phpmyadmin with regex or would I need to download the .sql database and do it with something else?

Thanks.

[Chen Zhen]

Make sure you have a copy of the DB prior to any changes.

I'm not sure if I understand precisely what you're attempting to accomplish.

Are you trying to alter the custom embed BBCode content in every current post/topic, alter the actual topic ID's or both?

Assuming this is just for altering what's within your custom BBCode tags for: messages -> body
You can use custom server side code to manipulate MySQL data (ie. PHP) or you can use regexp on a MySQL command line.

MySQL command line (assuming the prefix is "smf_") :

Code Select Expand

UPDATE `smf_messages` SET `body` = CONCAT(
    SUBSTRING_INDEX(body, '[embed]', 1),
    '[/embed]',
    CAST(REGEXP_SUBSTR(body, '(?<=[embed])[0-9]+') AS UNSIGNED) - 500,
    '[/embed]',
    SUBSTRING_INDEX(body, '[embed]', -1)
)
WHERE body REGEXP '[embed][0-9]+[/embed]';

I have no idea if the above will work but it might give you something to test on a copy of the database to make sure it works as intended. If you can't figure it out with the above then I can delve into it further by running some tests on a localhost.
If you're using PHPMyAdmin, keep in mind that it's written in PHP & will time out for long queries unless you increase some PHP limits. Another option is to use MySQL via Bash which should avoid any script timeouts.