Set row equal to another 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 this possible?

Answer 1

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

Can be done (only in MySQL) without DELETE using UPDATE 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, you can also make (perhaps better) an ANSI-compatible way with self-connection:

 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 2

You can do this 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 

Alternatively, you can join the table back and update the values ​​yourself. those. set derived tabular field = page.field

Answer 3

It should be possible to write a stored procedure that will look at the metadata for the table in question and build the necessary SQL to update one row from another without the need for hard coding. (Get the set of all columns, flip it, etc.) But this seems like a very complicated process.

You can also do the same in the application code (PHP, Perl, C # or something else) if you cannot execute it using sprocs.