Concatenating two tables with a common unique field in MySql
The problem is this:
We have hosted a website with an active community of contributors. We were given an application and database dump and successfully launched the site on the new server and the DNS was switched.
The problem is that the database was out of sync in the time it took to get the files for us and switch DNS. Now that the DNS has switched and there is no way to get out of database sync, we have been passed members2, which is a table from the original server with additional data.
Both tables look like this:
`idmembers` int(10) unsigned NOT NULL auto_increment,
`firstName` varchar(20) default NOT NULL,
`lastName` varchar(20) default NOT NULL,
`email` varchar(255) default NOT NULL,
`date` varchar(10) default '0',
`source` varchar(50) default 'signup'
PRIMARY KEY (`idmembers`),
UNIQUE KEY `email` (`email`)
So, the first table is called members1 and is an active database, in which there is no loading of members from members2. I need to string them together, keeping members1 as they are, and allowing unique emails from members2 to be inserted into members1.
I assume there is SQL for this, but I have no idea what it might be.
My second and less preferred approach would be to use a tool like PhpMyAdmin to export all entries from members2 after a specific date and reimport them to members1, but the problem is they are all exported from members2 with idmembers that conflict with members1 (in both are used as autoincrement)
source to share
If I understand your question correctly, there are two separate questions here:
- Adding completely new member posts from members2 to members1
- Update email field in members1 if it changed in members2
As in the first case, you should do something like:
INSERT INTO members1 ('idmembers', 'firstname', etc.)
SELECT 'idmembers', 'firstname', etc.
FROM members2
WHERE idmembers NOT IN (SELECT idmembers FROM members1)
As for the second case, something like:
UPDATE members1 m1 LEFT JOIN members2 m2
ON m1.idmembers = m2.idmembers
SET m1.idmembers = m2.idmembers
WHERE m2.idmembers IS NOT NULL AND m2.idmembers != m1.idmembers
(Note1: Both statements are "ad hoc" and are unverified!)
(Note 2: Both statements assume that the primary key "idmember" did not change during member migration1! If this happens, these queries will not work.)
(Note3: If you are facing the issue of "different key keys" from Note2, you can still use queries, but change the compare and join operations to use the email field. But then you need to run the second query first to prevent duplicates)
source to share
The most important suggestion is to do this on a copy of your database, not in a live database , until you are sure the process results in a merge fix!
You must first check if there are 2 lines in members with duplicate email addresses that already exist in members1:
SELECT members2.*
FROM members1 JOIN members2 USING (email);
If there are any (hopefully few) please fix them manually or remove each line, which is actually a duplicate account of the person who already has an account at members1 (keep the backup data of course).
If there are other cases of redundant member accounts that should be treated as duplicates and not inserted as new members, you may need to handle this manually. This is an example of the broader problem of cleaning up or removing duplicate databases, which usually cannot be fully automated.
You can copy rows from members2 to members1 when generating new id values like this:
INSERT INTO members1 (`firstName`, `lastName`, `email`, `date`, `source`)
SELECT `firstName`, `lastName`, `email`, `date`, `source`
FROM members2;
Yes, you must name all columns. Omitting idmembers
from this query, this column will use its default behavior, which should generate a new id value.
You didn't say that you need to update other tables that reference these new members by their ID. If so, you must create a new table to map members2 to the new number generated when importing into members1. You will need to follow @ijclarkson's guidelines for inserting members one at a time, so you can mark the new generated id.
SELECT * FROM members2;
-- loop over results in a script:
INSERT INTO members1 (`firstName`, `lastName`, `email`, `date`, `source`)
VALUES (?, ?, ?, ?, ?);
INSERT INTO members_id_map (idmembers1, idmembers2)
VALUES (LAST_INSERT_ID(), ?); -- use idmembers from the query on members2
-- end loop
source to share