CODEWITHSUNDEEP
mysqlsqldatabase
ONOZ
ONOZ

Reputation: 1410

Merging two tables with a relationship

I am building upon an existing database with a relationship construction I've seen nowhere before.

I have three tables:

legend1
legid       INT(11), AUTO_INCREMENT, PRIMARY
description VARCHAR(255)

legend2
legid       INT(11), AUTO_INCREMENT, PRIMARY
description VARCHAR(255)

items
id          INT(11), AUTO_INCREMENT, PRIMARY
name        VARCHAR(255)
legid       INT(11)
legend      VARCHAR(8)

Every record in items relates to data in either legend1 or legend2. The field items.legend determines wich one it is. I want to get rid of this construction as legend1 and legend2 have an identical structure. The only thing different is the content.

I want to have this construction:

legend
legid       INT(11), AUTO_INCREMENT, PRIMARY
description VARCHAR(255)

items
id          INT(11), AUTO_INCREMENT, PRIMARY
name        VARCHAR(255)
legid       INT(11)

The problem is that the tables are full and no data may be lost. The id of both tables starts on 1 so almost every primary key will collide.

I have this query:

INSERT INTO legend1 (description) SELECT description FROM legend2;

This query doesn't work because it messes up referenced id's from legend2.

Upvotes: 0

Views: 98

Answers (1)

Rick Hoving
Rick Hoving

Reputation: 3575

After you have executed your insert query:

INSERT INTO legend1 (description) SELECT description FROM legend2;

Perform the following query

UPDATE items SET legid = (SELECT legid FROM legend1 WHERE legend1.description = items.description) WHERE legend ='something to define that it is from the legend2 table'

Note that I haven't tried the query out but the solution is somewhat like this. If you pick out the syntax errors I've made I'm sure it will work.

What it does is the following: After you insert your entire legend2 table into the legend1 table you update your items table to set the corresponding legendid

Upvotes: 1

Related Questions