Hi All

Can I ask how best to update one MySQL database from another. Both databases use the same tables as they are from the same shopping cart though the content is somewhat different.

DROP TABLE IF EXISTS cscart_access_restriction_emailcc;
CREATE TABLE `cscart_access_restriction_emailcc` (
`item_id` int(11) unsigned NOT NULL auto_increment,
`value` varchar(66) NOT NULL default '',
`type` char(3) NOT NULL default '',
`avail` char(1) NOT NULL default 'Y',
`timestamp` int(11) NOT NULL default '0',
PRIMARY KEY (`item_id`),
KEY `type` (`type`)
) ENGINE=MyISAM AUTO_INCREMENT=13 DEFAULT CHARSET=latin1;

To merge the two would i use the statement UPDATE tables instead of DROP TABLE IF EXISTS something like this

UPDATE tables cscart_access_restriction_emailcc;
CREATE TABLE `cscart_access_restriction_emailcc` (
`item_id` int(11) unsigned NOT NULL auto_increment,
`value` varchar(66) NOT NULL default '',
`type` char(3) NOT NULL default '',
`avail` char(1) NOT NULL default 'Y',
`timestamp` int(11) NOT NULL default '0',
PRIMARY KEY (`item_id`),
KEY `type` (`type`)
) ENGINE=MyISAM AUTO_INCREMENT=13 DEFAULT CHARSET=latin1;



Thanks in advance

it don't work that way...
When you make an update, you have to tell which column and which row(s) you want to update.

update {table} set {col}={value} where {key}={key_value}

In my opinion, what you need here is an insert into query.
This is a ms sql server 2000/2005 syntax, I don't know how it would react on another database:

insert into cscart_access_restriction_emailcc
select *
from cscart_access_restriction_emailcc

But of course you are bound to run into problems...

The item_id will certainly conflict between the 2 databases, and you cannot update them blindly, as for sure there are relations put between the data in the item table and those in your access_restriction table.

Simply put, there is no simple, easy and conflict free road to do what you need to do. You will need to write a script that will read each line of the new database, and insert it in the correct tables, with the good referential keys maintained.

Trivial if you have realized the schema yourself. Infernal if you have to understand every relationship and don't have any infos...

According to your scenario, it is not possible to merge 2 databases with simple SQL queries. You must have to write a script which will merge databases while maintaining integrity as tripy mentioned.