220
edits
Changes
Jump to navigation
Jump to search
→Removing duplicates from ipos table
WHERE NOT EXISTS(SELECT * FROM ipoduplicatekeys WHERE (ipospkey.pkey = ipoduplicatekeys.pkey));
--9490
To verify that there are no more duplicates create another table.
DROP TABLE iposcountdupes1;
CREATE TABLE iposcountdupes1 AS
SELECT issuer, issuedate, statecode, COUNT(pkey)
FROM iponoduplicates
GROUP BY issuer, issuedate, statecode;
\COPY iposcountdupes1 TO 'iposcountdupes1.txt' WITH DELIMITER AS E'\t' HEADER NULL AS '' CSV
You will notice that there are still 9 duplicate records that slipped thru somehow. Remove these manually.
DELETE FROM iponoduplicates WHERE pkey = 'PacTel Corp1993-12-02CA$299.00';
--1
DELETE FROM iponoduplicates WHERE pkey = 'Templeton Dragon Fund Inc1994-09-21FL$169.50';
--1
DELETE FROM iponoduplicates WHERE pkey = 'Sterling Commerce1996-03-08TX$240.00' AND proceedsamt = '288';
--1
DELETE FROM iponoduplicates WHERE pkey = 'Sothebys Holdings Inc1988-05-13NY$27.00';
--1
DELETE FROM iponoduplicates WHERE pkey = 'TD Waterhouse Group Inc1999-06-23NY$655.20';
--1
DELETE FROM iponoduplicates WHERE pkey = 'Berlitz International Inc1989-12-13NJ$34.65';
--1
DELETE FROM iponoduplicates WHERE pkey = 'Spain Fund Inc1988-06-21NY$39.00';
--1
DELETE FROM iponoduplicates WHERE pkey = 'Ultramar Corp1992-06-26CT$85.39';
--1
DELETE FROM iponoduplicates WHERE pkey = 'Goldman Sachs Group Inc1999-05-03NY$424.00';
--1
iponoduplicates is now the master ipo table that contains unique keys which are concatenated from issuer, issuedate and prinicpalamt. It should contain 9481 lines.
