By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
437,890 Members | 1,050 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 437,890 IT Pros & Developers. It's quick & easy.

Forgot how to do a mass update in mySQL

P: n/a
Wm
I can't remember if I did this last time in phpMyAdmin or if I did it some
other way... I have 2 new fields in a database that I need to fill with
values. One field needs to have the default "0" updated to "1" in every
field. The other one I want to insert a randomly generated password. The
password function is in a form for all subsequent record insertions, but I
want to populate the existing 1100 records with one also. What's the
easiest way to fill these 2 fields without wiping out my data?

Thanx,
Wm

Jul 16 '05 #1
Share this Question
Share on Google+
3 Replies


P: n/a
Wm
That solved both problems -- THANX!

Wm

"Tom Thackrey" <to***@creative-light.com> wrote in message
news:mC*******************@newssvr13.news.prodigy. com...


On 9-Aug-2003, "Wm" <LA*******@hotmail.com> wrote:
I can't remember if I did this last time in phpMyAdmin or if I did it some other way... I have 2 new fields in a database that I need to fill with
values. One field needs to have the default "0" updated to "1" in every
field. The other one I want to insert a randomly generated password. The
password function is in a form for all subsequent record insertions, but I want to populate the existing 1100 records with one also. What's the
easiest way to fill these 2 fields without wiping out my data?


1- update mytable set newfield=1
or
1a- update mytable set newfield=1 where newfield=0
2- update mytable set passwd=RIGHT(PASSWORD(RAND()),8)

FYI the use of PASSWORD in no. 2 is coincidental to its being a password
field, also you can adjust the length of the password by changing the 8 to
whatever you like.
--
Tom Thackrey
www.creative-light.com

Jul 16 '05 #2

P: n/a
On Sun, 10 Aug 2003 13:18:32 -0500, in message
<Y%************************@news.easynews.com>,"Wm "
<LA*******@hotmail.com> wrote:
"Tom Thackrey" <to***@creative-light.com> wrote in message
news:mC*******************@newssvr13.news.prodigy. com...

[...]
1- update mytable set newfield=1
or
1a- update mytable set newfield=1 where newfield=0 2- update mytable
set passwd=RIGHT(PASSWORD(RAND()),8)


That solved both problems -- THANX!


And it was very educational for me. Thanks.

Jul 16 '05 #3

P: n/a

On 12-Aug-2003, "Wm" <LA*******@hotmail.com> wrote:
OK, now that the initial update is done, is there any way to format the
data
that is already in mySQL? In phpMyAdmin, I've tried various combinations
like:
update backup set email=STRTOLOWER(email)
or
update backup set email=STRTOLOWER('email')
with obviously no success. I know that this is a PHP command, but is there
any way to apply formatting to the data already in mySQL?


update backup set email=lower(email)

you might find the MySQL manual useful in restoring your fading memory ;->

http://www.mysql.com/doc/en/String_functions.html

--
Tom Thackrey
www.creative-light.com
Jul 16 '05 #4

This discussion thread is closed

Replies have been disabled for this discussion.