Register Members List Search Today's Posts Mark Forums Read

Thread Tools
Old 25 Nov 2017, 11:21
postcd postcd is offline
Join Date: Feb 2012
Help mysql query to change post/thread owner


i have two users who posted topics where they mention phrasse:

my aim is either delete such posts (but it seems to be too complicated as you can see there) or to change these topics/posts owner/user using mysql query.

SELECT * FROM `post` WHERE `pagetext` LIKE ''
it found 2 users which have matching posts:

username: username1, username2
userid: 479, 0

(username2 had userid 308 before i wrongly removed his account, keeping his posts)

SELECT * FROM `thread` WHERE `postusername` LIKE 'username1' AND `postuserid` LIKE '308' AND `replycount` LIKE '0' AND `notes` LIKE 'Imported thread'
This found posts i want to attribute to different user with id example: 308.

So as a total mysql noob, i tried:
UPDATE `thread` SET `postuserid` = '308' WHERE `postusername` LIKE 'username2' AND `postuserid` LIKE '308' AND `replycount` LIKE '0' AND `notes` LIKE 'Imported thread'
but is wrong. Can you please help with right mysql query/ies to change user id of the topics/posts that contain phrasse Thank you
Reply With Quote
Old 25 Nov 2017, 15:14
Dave Dave is offline
Join Date: Jun 2010
LIKE is used to check if a column contains a string, but your usage in the UPDATE query is wrong and I don't think that's what you really want to use anyway.

To match a value, you use =.
WHERE postusername = 'username2' AND postuserid = 308 AND replycount = 0 AND notes = 'Imported thread'.

Just keep in mind that you also need to update the userid in the post table. (First post of the thread)
Reply With Quote
Old 25 Nov 2017, 16:14
Seven Skins's Avatar
Seven Skins Seven Skins is offline
Join Date: Sep 2008
Don't know if this is what you are looking for.

SELECT * FROM `post` WHERE `pagetext` LIKE '' AND `userid` = 0;

update `post` SET `userid` = '308' WHERE `pagetext` LIKE '' AND `userid` = 0

See attachments for the results.. both queries returned same number of results.

Attached Images
File Type: png 001810.png (10.0 KB, 20 views)
File Type: png 001811.png (10.6 KB, 20 views)
Reply With Quote

Currently Active Users Viewing This Thread: 1 (0 members and 1 guests)
Thread Tools

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off

New To Site? Need Help?

All times are GMT. The time now is 12:25.

Layout Options | Width: Wide Color: