0

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...

2 Answers 2

0

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.

0

There's no FROM clause in UPDATE syntax. What I suggest you to do is an INSERT ... SELECT

INSERT INTO documentRelNew (documentID, dataType, studentID, teacherID, vendorID)
SELECT documentID, dataType, -1, dataID, -1
FROM documentRelOld
WHERE dataType=2;

I can't test this right now but I hope it helps to get you on the way.

2
  • The problem is that this only works for the one dataType. What happens for dataType 1, 2, 3, and so on. Each will have their own insert which makes this impossible :( Jul 15, 2016 at 20:01
  • In that case, you could use a CASE statement, presuming dataType for student is 1, for teacher is 2 and so on, you change the SELECT to: SELECT documentID, dataType, CASE dataType WHEN 1 THEN dataID ELSE -1 END CASE, CASE dataType WHEN 2 THEN dataID ELSE -1 END CASE, CASE dataType WHEN 3 THEN dataID ELSE -1 END CASE You won't need the WHERE clause. Either way, you have to map which dataType will define where the dataID goes. Jul 15, 2016 at 20:21

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.