Mysql – How to convert from one type of relationship table to another type

MySQLupdate

I currently have a relationship table where it's:

documentID, dataType, dataID

Where documentID is the link to the document file. The dataType is if the data is a specific type of user, for example a student, teacher, vendor, etc. (as these all have different tables). The dataID is the ID of the user.

So for example:

documentID, dataType, dataID
12343, 2, 4323

Would be the file 12343 for the type teacher (type 2 equals teacher) and it would be for teacher with ID 4323.

What I want to do is convert this table to the format:

documentID, dataType, studentID, teacherID, vendorID, etcID...
12343, 2, -1, 4323, -1, -1, ...

The reason I want to do it this way is because the files are now going to be cross-referenced a lot more and in the majority of cases a specific document will be linked to several different users and user types.

I could keep the original table but I would prefer to keep the inserts, updates, etc. to a single database to the document relationship table. That and a few other reasons.

Anyway is there a way to convert from one table to the other? The only possible solution I came up with is to transfer all the documentID and dataTypes in one step. Then transfer the dataID using a combination of matching document ID with dataType, and running one update per dataType. At least in theory… I haven't yet been able to make that work. Is this the only way and if so any suggestions as to the actual SQL would be appreciated. So far I have:

INSERT INTO documentRelNew (documentID) SELECT documentID FROM documentRelOld;

which works great but the following SQL line fails:

UPDATE 
    documentRelNew 
SET 
    documentRelNew.studentID=documentRelOld.dataID 
FROM 
    documentRelOld 
WHERE 
    documentRelOld.dataType=2;

It fails at the FROM portion. Yet everything I've read seems to indicate this should work. In theory I need to run this for all the different data types however I can't even get it to work for the first dataType I want to test…

Best Answer

You can add columns to the existing tables. These should be initially nullable until the data is loaded. After that you can alter the column definition to NOT NULL if appropriate. You can also drop columns from the table definition.

The process I use is:

Add new columns to table definition.
Populate new columns. (Before using code that expects these columns to be populated.)
Alter old NOT NULL columns to NULL. (Before using code that doesn't populate these columns.)
Make new columns NOT NULL where appropriate. (After code that doesn't populate these columns has been retired. May require rerun of population scripts.)
Drop old columns. (Can be done much later.)

These steps may be done over a period of days or weeks. This depends on your development process. The population script should be able to identify and populate rows created or modified by old code.

Find the name of all recipients of messages sent by user Mike Johnson

(edited to give better aliases to the two different uses of the user table)

SELECT recipient.firstname, recipient.lastname
FROM user recipient
JOIN message_recipient mr ON recipient.id = mr.user_id
JOIN message m ON m.id = mr.message_id
JOIN user sender ON m.user_id = sender.id
WHERE sender.firstname = 'Mike' and sender.lastname = 'Johnson'

Mysql – Update one table from another table while sorting that table based on one column

There are two options

OPTION #1 : INSERT INTO ... ON DUPLICATE KEY UPDATE

INSERT INTO newtable
SELECT * FROM oldtable
ORDER BY tmstamp_value
ON DUPLICATE KEY UPDATE email=VALUES(email), address=VALUES(address) ... ;

OPTION #2 : REPLACE INTO

REPLACE INTO newtable
SELECT * FROM oldtable
ORDER BY tmstamp_value;

It mechanically performs a DELETE and an INSERT.

CAVEAT

Notice I applied the ORDER BY against the old table. This should help get the latest data for a give LastName, FirstName.

You should also add a UNIQUE INDEX on the new table as follows:

ALTER TABLE newtable ADD UNIQUE INDEX name_ndx (lastname,firstname);

UPDATE 2013-04-08 17:13 EDT

If you are doing updates, there are two ways to do this:

1. UPDATE JOIN

UPDATE
(SELECT lastname,firstname,phone,email
FROM oldtable ORDER BY lastname,firstname) A
LEFT JOIN newtable B USING (lastname,firstname)
SET B.phone=A.phone,B.email=A.email;

2. UPDATE JOIN with GROUP BY

UPDATE
(
    SELECT BB.id,BB.phone,BB.email FROM 
    (SELECT lastname,firstname,MAX(id) id FROM oldtable GROUP BY lastname,firstname) AA
    INNER JOIN oldtable BB USING (lastname,firstname)
) A INNER JOIN newtable B USING (id)
SET B.phone=A.phone,B.email=A.email;

Give it a Try !!!

Both options will take full advantage of the UNIQUE index on name.