Update Table1 From Table2

[IntegrityWebDev]

I have 2 tables of identical structure in MySQL:
Table1 (the live table)
Table2 (the backup)
fields = id, a, b, c, d, e

Using PHP some fields in Table1 are editable by the end user. However I want to add a RESTORE button so that if they screw something up they can go back to the original data.

So I need a way (for example) to replace the entire row id=4 of Table1 with the original data from Table2

What is the best way to do this?

Thanks!

[Demaestro]

How I would do it is record all changes and display the last change to the user. If they "screw up" then simply delete that row and the one before it is restored by default.

Then if you want to keep things clean you can write a script that runs once a month and deletes changes that are 11 changes old or you could do it on time and delete anything older than X months... it depends on your data.

But basically it goes like this.

Table1 (The reference)
fields = rid, and all fields not editable

Table2 (The lookup)
fields = uid, rid, a, b, c, d, e, user_id (of person who made changes), timestamp (datetime of entry)

When someone makes an update you simply insert a new row into Table2

When you grab the data for viewing you just grab the most recent row from Table2

Something like.....

select tbl1.* tbl2.a, tbl2.b, tbl2.c, tbl2.d, tbl2.e
from
Table1 tbl1 left join Table2 tbl2 on (tbl1.rid = tbl2.rid and tbl2.uid = (select uid from Table2 where rid = THE_ID order by timestamp desc limit 1)

[IntegrityWebDev]

What I'm wanting to do is actually bit simpler (granted its lest robust but for what I need I dont need the revisions.) I just need to brute force overwrite one row of a table with a row from another table. I was wondering if there was a simple query for that.

[Demaestro]

What DB are you using?

I think MySql doesn't support update/selects

but what you want is

UPDATE Table1 tbl1, Table2 tbl2
SET tbl1.a = tbl2.a, tbl1.b = tbl2.b, and so on

WHERE tbl1.id = tbl2.id

[IntegrityWebDev]

I am using MySQL

[Demaestro]

Does that syntax work?

[IntegrityWebDev]

I couldn't get that to work...I ended up with just using 2 SQL queries in my PHP...one to pull out of the backup table, one to put that info in the "live" table and that worked. Thanks!