set row equal to other row in mysql?

Question

Here is the current state of my table:

mysql> select * from page;
+----+----------+----------------+------+---------+
| id | title    | body           | page | visible |
+----+----------+----------------+------+---------+
|  1 | my title | my body        | NULL |       1 | 
|  2 | my title | my body edited |    1 |       0 | 
+----+----------+----------------+------+---------+
2 rows in set (0.00 sec)

I want row 1 to contain the values of row 2. Basically, I want to do:

UPDATE page SET page.* = (SELECT * FROM page WHERE id = 2) WHERE id = 1;

Is something like this possible?

Answer 1

You could do it with two statements.

Delete from page where id = 2
insert into page (id, title, body, page, visible) Select 2, title, body, page, visible
from page where id = 1

Alternately, you could join the table back on itself and explicitly update the values. i.e. set derivedtable.field = page.field

Answer 2

insert into page (id, title, body, page, visible) Select 2, title, body, page, visible

Can be done (in MySQL only) without the DELETE by using an ON DUPLICATE KEY UPDATE:

INSERT
    INTO page (id, title, body, page, visible)
    SELECT 1, title, body, page, visible FROM page WHERE id=2
    ON DUPLICATE KEY UPDATE
        title= VALUES(title), page= VALUES(page), visible= VALUES(visible);

However can also done (perhaps better) in an ANSI-compliant way with a self-join:

UPDATE page AS page1 JOIN page AS page2 ON page1.id=1 AND page2.id=2
SET page1.title=page2.title, page1.body= page2.body, page1.page= page2.page, page1.visible=page2.visible

Answer 3

It should be possible to write a stored procedure that would look at the metadata for the table in question, and construct the necessary SQL to update one row from another without having to hardcode it. (Get the set of all columns, loop over it, etc.) But that seems like an awful lot of work.

You could also do the same thing in application code (PHP, Perl, C#, or whatever), if you can't accomplish it with sprocs.