Register Members List Search Today's Posts Mark Forums Read

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

Hello,

i have two users who posted topics where they mention phrasse: news.google.com

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 '%news.google.com%'
it found 2 users which have matching posts:

username: username1, username2
userid: 479, 0
pagetext: %news.google.com%

(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 news.google.com? Thank you
Reply With Quote
  #2  
Old 25 Nov 2017, 14:14
Dave Dave is offline
 
Join Date: Jun 2010
Real name: Dave
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)
__________________
https://technidev.com - security, development, exploits, vBulletin
dave[at]technidev[dot]com

Contact me for custom vBulletin 3/4 work & server/website management.
Reply With Quote
  #3  
Old 25 Nov 2017, 15: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 '%news.google.com%' AND `userid` = 0;

update `post` SET `userid` = '308' WHERE `pagetext` LIKE '%news.google.com%' 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
Reply



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 10:34.

Layout Options | Width: Wide Color: