SQL Statement to update Text within 'post_text' field.

Get help with installation and running phpBB 3.0.x here. Please do not post bug reports, feature requests, or MOD-related questions here.
Suggested Hosts
Forum rules
END OF SUPPORT: 1 January 2017 (announcement)
Locked
User avatar
GoleyC
Registered User
Posts: 374
Joined: Thu Mar 14, 2002 5:22 pm
Location: Ohio
Contact:

SQL Statement to update Text within 'post_text' field.

Post by GoleyC »

We have recently update our web site and need to update over 4000+ post that have a URL that is no longer valid. We need to find and replace just that URL to point to the new location.

We tried the following SQL statement, but it did not work. What is wrong with it?

Code: Select all

UPDATE post_text
SET DBpost_text = REPLACE(CAST(DBpost_text AS varchar(MAX)), 'old_url', 'new-url')
FROM phpbb3_posts
WHERE CHARINDEX('old-url',CAST(DBpost_text as varchar(MAX)))>0 
CG

User avatar
AmigoJack
Registered User
Posts: 5697
Joined: Tue Jun 15, 2010 11:33 am
Location: グリーン ヒル ゾーン
Contact:

Re: SQL Statement to update Text within 'post_text' field.

Post by AmigoJack »

GoleyC wrote:it did not work
Too little detail.
  1. Did you get an error messages?
  2. Was the query itself successful, but simply didn't affect any rows?
  3. Have you made sure your URLs were substituted for phpBB (dot and colon, i.e. http://www.mysite.com/path/site.html must be http://www.mysite.com/path/site.html)?
The worst thing about censorship is ███████████
Affin wrote:
Tue Nov 20, 2018 9:51 am
The problem is probably not my English but you do not want to understand correctly.
...
We will not come anybody anyway, nevertheless, it's best to shit this.

User avatar
GoleyC
Registered User
Posts: 374
Joined: Thu Mar 14, 2002 5:22 pm
Location: Ohio
Contact:

Re: SQL Statement to update Text within 'post_text' field.

Post by GoleyC »

We have another DB that we need to update and for some odd reason, the following query will not parse?

Code: Select all

UPDATE phpbb3_posts SET post_text = (REPLACE (post_text, ‘/press_releases/’, ‘/press-releases/’));
We keep getting the following error:

Code: Select all

#1054 - Unknown column '‘' in 'field list'
However, the post_text is the correct column. So I'm at a complete loss.
CG

Locked

Return to “[3.0.x] Support Forum”