XF 2.0 SQL query to update all thread titles?

[sbj]

Hello,
I tried this:

UPDATE xf_thread SET xf_thread.title=xf_thread.title WHERE xf_thread.node_id = 36;

and this (which is the same query I think):

UPDATE xf_thread SET title=title WHERE node_id = 36

The query runs but it says 0 rows are affected.

Basically what I want is, like when you edit a thread-title and press save but without changing anything. Just pressing "save" in a SQL query for all threads in a node.

Is this possible? Or another way to batch update thread titles?

Yes, I do have a backup of my database. Any help is appreciated.

Thank You.

 

[S Thomas]

Real question would be why you want to touch (that's what it's called) thread titles. Did you try to rebuild thread & post information in the ACP?

 

[nocte]

I suppose this query won't work because you don't set a specific title. Maybe with a sub-query where you SELECT the title of specific thread:

UPDATE xf_thread SET title=(SELECT title FROM xf_thread WHERE thread_id = 1) WHERE node_id = 36;

no warranty

 

[S Thomas]

This would set the title of all threads in node 36 to the one with id 1, certainly not what is called a touch Besides that you'd need to nest the sub query.

 

[sbj]

Real question would be why you want to touch (that's what it's called) thread titles. Did you try to rebuild thread & post information in the ACP?

I don't want to rebuild them, want to "touch" them. And yes, I also tried rebuilding but it doesn't update the threads like I want.
Because I have a script in order to make thread titles follow title case rules (with exceptions and language speficic rules).
Now, this script works fine for new created threads, cause this script is now in place and any new saved thread title gets manipulated and all looks fine.
But all old threads, threads created before this script have their ugly thread titles. Now I can go edit thread titles manually and press save, which makes those old threads get the right shape and form, but I can't do that manually for thousands of threads naturally. So I need a query to do that.

I suppose this query won't work because you don't set a specific title. Maybe with a sub-query where you SELECT the title of specific thread:

UPDATE xf_thread SET title=(SELECT title FROM xf_thread WHERE thread_id = 1) WHERE node_id = 36;

no warranty

No, I don't want to change any thread title, this would change those titles. I just want to "bump" the thread titles, without changing anything.

 

[nocte]

No, I don't want to change any thread title, this would change those titles. I just want to "bump" the thread titles, without changing anything.

oh, sorry, should not answer in SQL Threads

Anyway: and the logic that changes the thread titles is in your addon? If so, I suppose you have to work from the PHP (addon) side because the Mysql database does not know about that logic. Or did I misunderstand..

 

[sbj]

The addon does his job already. This is outside of the addon.

If so, I suppose you have to work from the PHP (addon) side because the Mysql database does not know about that logic.

But there must be a query send from XenForo to update the titles. Just open a thread, type any title you want and save it. Then go back to the thread, open the overlay by using "edit thread" and save it without changing. That query to update the thread title is what I am looking for. There must be a SQL query or how else would those titles get updated?

Is it possible to change your suggested query @nocte? Like instead of selecting the title from thread_id = 1, letting it select automatically for each row? So select title what is there and replace it with the same thing, that would update the threads, wouldn't it? I just need to find a way to do that for all rows.

 

[nocte]

No, that won't work that way..

You have to write a script/addon that does that for all threads at once (and run it once): Load the thread entities and save them one after another. That might be sufficient if the addon hooks into the right places.

I would ask @AndyB - he's the creator of the addon that you use.

 

[sbj]

I already asked him but it is rightfully out of the scope of that addon. He told me that it is not possible without a new script/addon.
But I thought with my limited knowledge that there must be a SQL Query to just update existing columns without going the php way.
If it is not the case, all good. Better asking and knowing that the outcome is not possible I want to have rather than having no definite answer.

Thank you.

 

[S Thomas]

The SQL query will never respect your restrictions from script side. Thus, you will need to trigger the title edit function yourself for every thread. You can write a script for that, though.

 

[sbj]

The SQL query will never respect your restrictions from script side.

That wasn't needed anyway. The script gets triggered by every thread title update. The SQL query didn't have to care about the script side as long as there would be a SQL Query to "touch" thread titles. If that existed, the script would be triggered by all the thread title updates and adjust the titles.
But there is no query, so the script can't run.

Thus, you will need to trigger the title edit function yourself for every thread. You can write a script for that, though.

Well, if I could write my own script, I wouldn't ask in first place . I thought there is a shortcut without writing a script, but there isn't. It is ok, I gave the idea up already. Thanks for helping me though.

 

[nocte]

That wasn't needed anyway. The script gets triggered by every thread title update. The SQL query didn't have to care about the script side as long as there would be a SQL Query to "touch" thread titles. If that existed, the script would be triggered by all the thread title updates and adjust the titles.

No! You don't have an idea what CRUD means, right? 3 users told you it won't work and you still don't believe it?!

The magic you expect won't happen. To verify this you can go to phpMyAdmin and update any entry in xf_thread - and you'll see: nothing will happen automatically..

 

[sbj]

3 users told you it won't work and you still don't believe it?!

???
I think you misunderstood something. I do believe it and see:

I thought there is a shortcut without writing a script, but there isn't.