help with update sql

[dethfire]

I can do a select with most of this, but when I switch it to update, it chokes and I can't see where. any help?

UPDATE xf_thread
SET xf_thread.discussion_state = 'deleted'
FROM xf_thread
INNER JOIN xf_post
ON xf_thread.thread_id = xf_post.thread_id
WHERE xf_post.message
LIKE '%[Broken]%'
AND length(xf_post.message) < 1000
AND xf_thread.reply_count = 0

 

[Bespoke]

Try removing the FROM and moving the JOIN above the SET

UPDATE xf_thread AS thread
INNER JOIN xf_post AS post ON (thread.thread_id = post.thread_id)
SET thread.discussion_state = 'deleted'
WHERE thread.reply_count = 0
AND post.message LIKE '%[Broken]%'
AND length(post.message) < 1000

But if you are only after the first post of a thread why not just grab the first_post_id from the thread?

 

[dethfire]

Try removing the FROM and moving the JOIN above the SET

that works!

But if you are only after the first post of a thread why not just grab the first_post_id from the thread?

lol you are right

 

[Lukas W.]

WHERE xf_post.message LIKE '%[Broken]%' AND length(xf_post.message) < 1000 AND xf_thread.reply_count = 0

You may wanna swap your condition order. It's probably straightened out by the query optimization, but worst case you perform a full text search on strings that are too long for you to want to consider anyway, for the majority of threads that you don't bother about anyway.