View Full Version : Help with an update script

05-04-2009, 08:55 PM

I have two sets of tables in mysql database held_stories and new_stories.

Now, what I want to do is get all the data from held_stories and update new_stories with that information where the story_id's for each table match.

Can anyone help me please, I think i'm nearly there?



// Start query one and get all the details from held stories

$query_one = "SELECT story_id, headline from held_stories";
$result = mysql_query($query_one) OR die(mysql_error());
while ($row = mysql_fetch_array($result)) {
$story_id = $row['story_id'];
$headline = $row['headline'];

//Start the second query to update the new stories with the held stories details

$query_two = "UPDATE new_stories set headline = '$headline' WHERE story_id = $story_id";
$result = mysql_query($query_two) OR die(mysql_error());

$n++; }

$number_of_results = mysql_num_rows($result);

If (mysql_num_rows($result) > 0) {
echo "We have updated <b>".$number_of_results."</b> stories in the new_stories database";
else {
$row = mysql_fetch_array( $result );
echo "No stories have been updated";


05-04-2009, 10:47 PM
Your UPDATE query inside the SELECT loop is assigning the query return value to $result, but you assigned the query return value of your SELECT query to $result. Your loop is blowing up after $result has been reassigned.

Though, there is an easy way to do this in one query:

UPDATE new_stories as n JOIN held_stories as h ON n.story_id = h.story_id
SET n.headline = h.headline