row copy from 1 db to another

[thompom]

Hi,
Want to copy a row from one database to another on different servers - have googled this and sql now looks like

INSERT 
  INTO server1.db.table
     (field1, field2)
SELECT *
  FROM server2.db.table
where id = '1'

unfortunatley i get error

Error Code : 1064
You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '.table

I think this is because both the source db/table names are the same as the target db/table names.
Is this correct?

How do i get round this?

thanks MG

 

[towerbase]

I don't think this is possible.

Where did google show you how this can be done?

Andrew

 

[thompom]

hi - thanks for your reply, is there a work around?
the reason is in case I need to restore rows from a backup
thanks MG

 

[towerbase]

There is probably no simple answer to this because it depends upon your database design and application / system requirements.

What are the circumstances that rows can be lost and need to be restored?

There are plenty of MySQL backup tools that can restore tables.

Andrew

 

[thompom]

Can restore whole tables no probs - is needed when someone "accidentally" deletes a row (which is rare)

 

[towerbase]

There are several possible solutions to this problem. A couple of suggestions are:

Each morning make a copy of the table on the server that the user is using. Allow the user the option of recovering his data from that copy.

When the user deletes a record, keep it in the table but have a deleted flag in the record which effectively hides the record from processing (except when the user wishes to recover it).

The main point is don't use the backup from another server as this is intended to recover from system/hardware failures and not from users "accidentally" deleting records.

Andrew