mySQL updating one field

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
ronack
Forum Newbie
Posts: 8
Joined: Tue Jan 06, 2004 5:42 pm

mySQL updating one field

Post by ronack »

Is there a way to update one field for every record in a in a db of over 1000 without having to do every field? (does this question make since?)

I'm new at this so keep it simple....
Straterra
Forum Regular
Posts: 527
Joined: Mon Nov 24, 2003 8:46 am
Location: Indianapolis, Indiana
Contact:

Re: mySQL updating one field

Post by Straterra »

ronack wrote: a db of over 1000
1000...what? Rows, collumns, tables, databases?
ronack
Forum Newbie
Posts: 8
Joined: Tue Jan 06, 2004 5:42 pm

Post by ronack »

Sorry Rows
almostsane
Forum Newbie
Posts: 13
Joined: Sat Jan 10, 2004 3:26 am

Post by almostsane »

using PHP,

Code: Select all

<?php
$db = mysql_pconnect("hostname", "username");
mysql_select_db("database");
$result = mysql_query("select * from tablename"); //get exact number of rows

$row_num = mysql_num_rows($result);

for ($i = 0; $i < $row_num; $i++)
{
$query = "update tablename set fieldname = newvalue where Key = ".(string)$i;
mysql_query($query);
}

?>
that might work? can someone else comfirm before he tries it as i'm still kinda a noob to php and mysql
ronack
Forum Newbie
Posts: 8
Joined: Tue Jan 06, 2004 5:42 pm

Post by ronack »

Thanks, I can probably use this, but I'm just looking for an sql statement to do it at the phpmyadmin panel. Like something else instead of

INSERT INTO `table` VALUES (27, 'text', 'text', NULL, '2004-01-06 00:00:00', 'blank.gif', 55, 'blank.gif', '', '', '', '', 0, 0, '', '');

This will insert one record.

The records are already there and I need to update one field. Maybe Changing blank.gif to image.gif on all the records.
User avatar
JAM
DevNet Resident
Posts: 2101
Joined: Fri Aug 08, 2003 6:53 pm
Location: Sweden
Contact:

Post by JAM »

Selfexplainatory:

Code: Select all

update table set imagefield = 'FOO.gif' where imagefield = 'BAR.gif'
Loose the where clause to update all fields.
http://www.mysql.com/doc/en/UPDATE.html
ronack
Forum Newbie
Posts: 8
Joined: Tue Jan 06, 2004 5:42 pm

Post by ronack »

Ok thanks

You have all been a great help.
almostsane
Forum Newbie
Posts: 13
Joined: Sat Jan 10, 2004 3:26 am

Post by almostsane »

yeah i realised after i went to bed you could just use

[mysql_man]

update table set imagefield = 'FOO.gif' where imagefield = 'BAR.gif'

[/mysql_man]
Post Reply