MySQL: Merge Tables on non primary column

I have two MySQL tables with identical structures that need to be merged.

The primary key is "bookkey" however the two tables should be merged on the column `isbn`.

I want rows from `booksrevised` to be added to `books` if the isbn value is not already in `books` and I want the rows in `books` to be updated to contain the values from `booksrevised` when the value of isbn is the same.
CREATE TABLE `books` (
  `bookkey` int(11) NOT NULL,
  `isbn` varchar(13) NOT NULL,
  `title` varchar(50) NOT NULL,
  `description` varchar(50) NOT NULL,
  PRIMARY KEY (`bookkey`)
);
INSERT INTO `books`(`bookkey`,`isbn`,`title`,`description`) VALUES
(1,'9780000000001','Hello','Hello World'),
(2,'9780000000009','Test','Example'),
(3,'9780000000022','Demo','Testing'),
(4,'9780000001412','Word','Test'),
(5,'9780000004235','Zooph','Hi');

CREATE TABLE `booksrevised` (
  `bookkey` int(11) NOT NULL,
  `isbn` varchar(13) NOT NULL,
  `title` varchar(50) NOT NULL,
  `description` varchar(50) NOT NULL,
  PRIMARY KEY (`bookkey`)
);
INSERT INTO `booksrevised`(`bookkey`,`isbn`,`title`,`description`) VALUES
(1,'9780000000001','Hello World','Hello World 123'),
(2,'9780000000126','New','New Record'),
(3,'9780000000022','Demo','Testing'),
(7,'9780000001419','ABC','XYZ'),
(8,'9780000004235','Zooph','Hi');

Open in new window

LVL 16
hankknightAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

johanntagleCommented:
I assume here it can never happen that you have the same isbn but different bookkey - only new books will have a bookkey that doesnt exist in books?  Maybe you should consider using auto_increment for your primary key?

Although books.isbn is not primary, that values for this column are still unique, right?  Then you can add a unique key to it.  

ALTER TABLE books add unique key (isbn);

Open in new window


This way you can use INSERT ON DUPLICATE KEY UPDATE:

INSERT books (`bookkey`,`isbn`,`title`,`description`) 
SELECT `bookkey`,`isbn`,`title`,`description`
FROM booksrevised
ON DUPLICATE KEY UPDATE title=values(title), description=values(description);

Open in new window


Come to think of it if the same isbn will also have the same bookkey between the two tables, then there's no longer any need for a unique key for isbn.  But if this is not the case, then you should use auto_increment for bookkey and do not select it from booksrevised.  This way you can avoid conflicts.

See http://dev.mysql.com/doc/refman/5.5/en/insert-on-duplicate.html for more details
0
Surranoapplication managerCommented:
If my understanding is correct bookkey is some artificial identifier and it *should* be the same in the two tables but it is not.

Also, I assume same ISBN  means same title and description, at least that's what you'd like to achieve with this merge.

I think the structure of your data is incorrect and that's what's causing you headaches.

Try the following:

1. Create one table containing details like title and description, with ISBN as primary key
2. Merge there
3. Drop columns title and description from the other two tables
0
mankowitzCommented:
Do you need to maintain bookkey? If not, you could do this:

alter table books add unique (isbn);
alter table books modify column bookkey int(11) auto_increment;
replace books (isbn, title, description) SELECT isbn, title, description from booksrevised;

http://sqlfiddle.com/#!2/69fa9/1
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
MySQL Server

From novice to tech pro — start learning today.