SQL search of thread titles for ...

[Dennis Olson]

I want to write a SQL search to scan all thread TITLES ONLY for specific text. If that text is at the START of the title, I want to modify/update a field with a specific value. Can you point me in the right direction? I don't wanna mess this up....

;^)

[Lats]

This assumes you want to update a user field to contain 'Thread Title started with Sorry' if a thread title starts with 'Sorry'...

UPDATE
userfield, thread
SET
userfield.field2 = 'Thread Title started with Sorry'
WHERE
thread.postuserid = userfield.userid
AND
thread.title
LIKE
'Sorry%'

Is that what you were wanting to do?

[Dennis Olson]

I do believe that's what I have in mind. I need to run some tests. I assume I can add "and thread.forumid = X" to specify which forum to process...?

[Lats]

Yes, that can be tacked on to the end, or for several forums...

...
AND thread.forumid IN (4,5,6)

[Dennis Olson]

Okay, here's what I want to do. Can someone please make sure my syntax is correct?

update thread.<a certain field> 
inner join post.threadid where post.parentid = 0 and post.title like 'text%';

The purpose of this code is to update a field in the thread table, using post.threadid as the key, and only if the post is the start of a thread (post.parentid = 0), and the text in the post.title contains specific text.

[Dennis Olson]

bump....

[Fataqui]

Okay, here's what I want to do. Can someone please make sure my syntax is correct?

 
update thread.<a certain field> 
inner join post.threadid where post.parentid = 0 and post.title like 'text%';

The purpose of this code is to update a field in the thread table, using post.threadid as the key, and only if the post is the start of a thread (post.parentid = 0), and the text in the post.title contains specific text.

Why would you use post.parentid in your WHERE clause? You already have a table relationship with (thread_firstpostid and post_postid) what your doing is selecting all the columns then only limiting the result based on the parentid which is more work for MySQL. Think of it this way. A relationship is to bring together (2) or more tables which (thread_firstpostid and post_postid) do, plus they only limit their relationship to threads that are the parents! After you have your relationship then you use the WHERE clause to ask your question title LIKE '??%'. If you want to limit based on the forum then add that to your ON() because it part of the relationship and not the question!

Anyway...

UPDATE bbs_thread AS t LEFT JOIN bbs_post AS p ON (t.firstpostid = p.postid) SET t.notes = 1 WHERE p.title LIKE '???%'

where as bbs_ would be your TABLE_PREFIX and t.notes = 1 would be replaced with what column you want to update in the thread table, and ??? would be replaced by what you are asking MySQL to do the UPDATE based on!

Sonia