Restoring data from one table to the other.

[Angelos]

Hello,
I have the a table (content) with some content and every time I edit it I
store all the table contents in an other table (content_bak) when I want to
restore the contents
How am i going to do that ? I am trying to do :

REPLACE INTO content SELECT bak_content_id, bak_content_title FROM
content_bak WHERE backup_id = '".$_GET['buckup_id']."'";

TABLE CONTENT
content_id int PK
content_title varchar

TABLE CONTENT_BAK
backup_id int PK
bak_content_id int
bak_content_title varchar

What I want to do is to REPLACE the TABLE CONTENT content_id = $x with
the TABLE CONTENT backup_id = $y AND bak_content_id =$x

Any ideas ?

[Bill Karwin]

Angelos wrote:

Hello,
I have the a table (content) with some content and every time I edit it I
store all the table contents in an other table (content_bak) when I want to
restore the contents
How am i going to do that ? I am trying to do :

REPLACE INTO content SELECT bak_content_id, bak_content_title FROM
content_bak WHERE backup_id = '".$_GET['buckup_id']."'";

TABLE CONTENT
content_id int PK
content_title varchar

TABLE CONTENT_BAK
backup_id int PK
bak_content_id int
bak_content_title varchar
I assume you mean that the PK is a compound key: (backup_id,
bak_content_id).
What I want to do is to REPLACE the TABLE CONTENT content_id = $x with
the TABLE CONTENT backup_id = $y AND bak_content_id =$x

If you're certain that all content_id values you need to restore still
exist in the content table (that is, no records have been deleted and no
new records have been added since the backup was made), you could try a
multi-table UPDATE:

UPDATE content, content_bak
SET content.content_title = content_bak.bak_content_title
WHERE content.content_id = content_bak.bak_content_id
AND content_bak.backupd_id = '$y'

See http://dev.mysql.com/doc/mysql/en/update.html

If you can't make the assumptions about content_id values remaining the
same (which is probably a safe bet), you could use the REPLACE method.
You should first get rid of any rows that don't appear in the backup,
since I assume you don't want a mix of old record and newer records.

DELETE FROM content
USING content LEFT OUTER JOIN content_bak
ON content.content_id = content_bak.bak_content_id
AND content_bak.backup_id = '$y'
WHERE content_bak.backup_id IS NULL

Then do the REPLACE, and name your columns more explicitly to ensure
that the fields match up from the backup:

REPLACE INTO content (content_id, content_title)
SELECT bak_content_id, bak_content_title
FROM content_bak
WHERE backup_id = '$y'

Does this not work?

A third possibility is to empty the content table, and then just do a
simple INSERT from the backup table. That ensures no crumbs left over.

DELETE FROM content;
INSERT INTO content (content_id, content_title)
SELECT bak_content_id, bak_content_title
FROM content_bak
WHERE backup_id = '$y'

Regards,
Bill K.

[Angelos]

> Regards,

Bill K.

Thanks a lot Bill !!!