updating several records::Please help

diana posted this at 12:43 — 24th December 2003.

They have: 3 posts

Joined: Dec 2003

Hi All,

First of all I would like to wish everyone Merry Cristmas and Happy Holidays.I am newbie both to this forum as well as php/mysql developemnt.

Currently I am doing project for my client..there I faced a problem which requires me to updates more than 12,000 records with a single query. A solution to replace 12,000 entries in one row of our current mySQL database with new info. We want to change the word "with" or "without" with the word "Yes" and "No", respectively...

I thought about mysql_query...but I think thats meant for only one query..not for batch update.

I am also confused a bit the query I should build?

I will definitely appreciate any help, because I am a newbie to php/mysql..so this problem is quite confusing for me.

Regards
Diana

mairving posted this at 13:11 — 24th December 2003.

They have: 2,256 posts

Joined: Feb 2001

Diana, welcome to WMF and Merry Christmas to you also. If you want to update records in MySQL assuming that your column contains values like 'with' or 'without' and not 'with other stuff' or without other stuff', in other words only a single word in those columns, then it is pretty easy to do with a simple query. Just do:
UPDATE tablename set column_name='Yes' where column_name='With';

Now this problem could have been solved from the beginning if the column type was either a single character (Y and N or 1 and 2). This way you can save a little database space and avoid some redundancy.

Mark Irving
I have a mind like a steel trap; it is rusty and illegal in 47 states

diana posted this at 13:56 — 24th December 2003.

They have: 3 posts

Joined: Dec 2003

Hi Mark,

Thanks for such quick reply.It looks so simple:)...will it update all the 12,000 rows?..say I have the query build up like...
$result=mysql_query("UPDATE users SET intro='YES' where intro='with', $link);

Now does the above query will update all the rows where 'intro' is found to be having a value 'with' and change that value to 'YES'....?

Well I am getting it....I seems to be such a novice:)...long way to go....thanks Mark...Merry Cristmas to you and your family..

regards
Diana

Greg K posted this at 02:50 — 25th December 2003.

He has: 2,145 posts

Joined: Nov 2003

That should do it for you, then don't forget to run a second query for chaning the WITHOUT to NO.

After you execute the query, you can check mysql_affected_rows($result) to see how many rows it updated.

-Greg

diana posted this at 04:36 — 25th December 2003.

They have: 3 posts

Joined: Dec 2003

Thanks Greg...I certainly appreciate all your help. Wish you Merry Christmas and Happy New Year. Happy holidays...

Rgds
Diana

Anonymous posted this at 00:06 — 1st April 2004.

They have: 5,633 posts

Joined: Jan 1970

Hi All,

I'm new to the board, new to MySQL, and have become the company 'webmaster' by default...

With that said, my situation is similar to Diana's, except that my column doesn't contain just one word. What I need to do is change a single word "cover" to "covers" in ever instance throughout the `description` field.

From: "blah blah blah cover blah blah"
To: "blah blah blah blah covers blah blah"

I have a feeling this is going to be WAY over my head..but thanks in advance for any help.

-john