Select value of one field into another on the same MySQL table in from the same row?

Question

If I were to translate my MySQL table into PHP it might look something like this:

$table = array();
$table[0] = array ('id'=>0, 'f1'=>0, 'f2'=>1);
$table[1] = array ('id'=>1, 'f1'=>0, 'f2'=>2);
// etc...

In that case, I would want to do something like this:

foreach($table as $row) {
    $row['f1'] = $row['f2'];
}

Is it possible to do this with a single MySQL statement utilizing select and update?

I was imagining something like this:

update myTable set f1=(select f2 from myTable where id=id);

Except I don't think that would work... I'm not sure how to say where id in the second statement is equal to the id in the first statement. Or how to apply it to all rows in the table.

How could I do this?

Answer 1

Just

UPDATE myTable SET f1 = f2

the expression on the right of the = is in terms of existing columns of the same row (one often does, e.g, SET col = col + 1... it doesn't have to be OTHER columns, though it can). The lack of a WHERE clause means the update will happen on all rows, as you seem to want.

Answer 2

Actually, to update the f1 values to be the same as the f2 ones you'd use:

UPDATE mytable
   SET f1 = f2

If you don't specify a WHERE clause, the query will apply to all rows.