Login or Register to Ask a Question and Join Our Community


UNIX for Dummies Questions & Answers

Mysql: How to update value in 27000 rows?

 
Thread Tools Search this Thread
Top Forums UNIX for Dummies Questions & Answers Mysql: How to update value in 27000 rows?
# 1  
Old 03-25-2014
[Solved] Mysql: How to update value in 27000 rows?
Hello,

some member created 27000 posts in wrong section (lol Smilie) so i need to edit all his entries to get new section ID.

Code:
SELECT * FROM `phpbb_topics` WHERE `topic_first_poster_name` LIKE "%ozerway%";

this will select all his topics...

the column with forum id is named "forum_id" and wrong value is 4, correct value should be 5

please kindly how the command should look like to update all these mysql rows forum_id from 4 to 5?

Thank you
Last edited by Scott; 03-25-2014 at 04:13 PM.. Reason: Use code tags
# 2  
Old 03-25-2014
Something like this should do the trick, if I read your post correctly:

Code:
UPDATE phpbb_topics SET forum_id="5" WHERE topic_first_poster_name LIKE "%ozerway%" AND forum_id="4" ;

Note (edit): Keep in mind that in many forums, table values might be used across multiple tables; so please insure that you only need to update forum_id in (the) one table in phpbb (you are considering updating).
This User Gave Thanks to Neo For This Post:
postcd
# 3  
Old 03-25-2014
Code:
UPDATE `phpbb_topics` SET forum_id=5 WHERE forum_id=4 AND `topic_first_poster_name` LIKE "%ozerway%";


Moderator's Comments:
Mod Comment Neo: There is no compelling reason to delete this (almost) duplicate reply. It shows a different order / perspective. So, I restored this post (undeleted) it.
# 4  
Old 03-25-2014
thanks for help, i backed up mysql and run the command.
It miraculously show topics in right section now Smilie
This User Gave Thanks to postcd For This Post:
Corona688
 
Login or Register to Ask a Question

Previous Thread | Next Thread

8 More Discussions You Might Find Interesting

1. Programming

MYSQL - trigger update on record insert or update

Right I have a MYSQL database with table1 with 3 columns, colA, colB and colC. I want to combine the data in the 3 columns into a 4th column names col_comb. Here's the SQL command that works: UPDATE table1 SET `col_comb` = CONCAT( `colA` , ' - ', `colB` , ', ', `colC` ); So now I want this... (5 Replies)
Discussion started by: barrydocks
5 Replies

2. Shell Programming and Scripting

MySql split rows

Dear community, I have to split string in table and list all values. I'll skip the code and jump directly to mysql query. This is the table: category title ======= ======= 7,3 title 1 1,3 title 2 1,2,3 title 3 Now, what I need is split category into single... (2 Replies)
Discussion started by: Lord Spectre
2 Replies

3. Shell Programming and Scripting

Retrieve multiple rows from mysql and automatically create a table

Hi, i want to create a table automatically based on another table (sms_key). For example; If user create a new row with sms_keyword field: IRC then a table created automatically (with some field on it, like: name, ph_number, messages). select * from sms_key; +-------------+ |... (1 Reply)
Discussion started by: jazzyzha
1 Replies

4. Programming

Getting Rows from a MySQL Table with max values?

I feel stupid for asking this because it seems that MYSQL code isn't working the way that I think it should work. Basically I wrote code like this: select * from `Test_DC_Trailer` HAVING max(DR_RefKey); Where the DR_RefKey is a unique numeric field that is auto iterated (like a primary key)... (7 Replies)
Discussion started by: Astrocloud
7 Replies

5. UNIX and Linux Applications

Online insert MySQL rows by perl-script

Hello, Met a problem when I tried to insert rows to MySQL database from an old book that fits my learning level (MySQL and Perl for the Web, by Paul DuBois, 2001). First, under mysql console I created a database: webdb and the table: todo. Then I draft the perl-cgi script to have online page.... (0 Replies)
Discussion started by: yifangt
0 Replies

6. Shell Programming and Scripting

Mulitple rows update by sed command

Hi All, I need to update 2 rows in my file. But i can do only one row update by sed command. Please help me how can i change mutltiple rows in single sed commana my i/p file: (example.txt) record integer (10) present_id; string (10) first_name; string (10) last_name; string... (2 Replies)
Discussion started by: pdathu
2 Replies

7. Web Development

[MYSQL] problem with spaces in rows

Hello. I'm not sure how I can get around this, or what I am doing wrong, but I need some help. :) I want to do an select query looking like this: SELECT venue, SUM( amount ) FROM IWD WHERE venue = 'Foxy Hollow' Unfortunately I need to have spaces in the names in these fields, is... (10 Replies)
Discussion started by: noratx
10 Replies

8. Web Development

Mysql question: Best way to update a column containing 8 million rows

Hi all, I was wondering if anyone knew a good/safe way to update a single column in a table that could contain upto 8 million rows... simple command like: UPDATE set blah=foo where bar=XXX; I will be running this on tables being written to and tables that have already been created. ... (3 Replies)
Discussion started by: muay_tb
3 Replies
Login or Register to Ask a Question