Last post Oct 18, 2013 08:47 AM by GrassProgrammer
Oct 17, 2013 04:27 PM|GrassProgrammer|LINK
I'm taking over a database from another employee. It wasn't designed properly, hence I'm trying to redesign it.
We have documents, which contain the name/number/address/etc of the person who made that document.
This person, the applicant, is often repeated, hence it's best to have a table just for applicants.
This caused all sorts of issues with dirty data. Spaces at the end, in the beginning, capitalisation, punctuation, everything. I spent 6 hours and cleaned up the data.
Now I just need to restructure.
- ApplicantName (Legacy)
- ApplicantPhone (Legacy)
- ApplicantCity (Legacy)
I need a query that will look at DOCUMENTS and see which name was in the legacy ApplicantName column. It will then search for that name in the APPLICANTS table, and put the associated ID in the DOCUMENTS.APPLICANTID column.
How would a query like this go? I'm having trouble figuring it out in my head.
Once the ID is populated, the legacy columns can be deleted.
Oct 17, 2013 06:23 PM|hans_v|LINK
Create the Applicant Table
CREATE TABLE Applicants (ID COUNTER NOT NULL, Name TEXT(255), Phone TEXT(255), City TEXT(255));
Create the Primary Key
CREATE INDEX [PrimaryKey] ON [Applicants]([ID]) WITH PRIMARY
fill the Applicants Table:
INSERT INTO Applicants (Name, Phone, City)
SELECT ApplicantName, ApplicantPhone, ApplicantCity
GROUP BY ApplicantName, ApplicantPhone, ApplicantCity
add the ApplicantID column to the table Document
ALTER TABLE Applicants ADD COLUMN ApplicantID Long
Update the ApplicantID
INNER JOIN Document
ON Applicants.Name = Document.ApplicantName
AND Applicants.City = Document.ApplicantCity
AND Applicants.Phone = Document.ApplicantPhone
SET Document.ApplicantID = Applicant.ID
Delete the Legacy columns
ALTER TABLE Documents DROP COLUMN ApplicantName
ALTER TABLE Documents DROP COLUMN ApplicantPhone
ALTER TABLE Documents DROP COLUMN ApplicantCity
Oct 18, 2013 08:47 AM|GrassProgrammer|LINK
One of the most complete answers I've seen in a long time. Thank you!
Worked like a charm. You even had the delete column queries too! :p