Clean
After importing the line listings there are duplicate records in the database in various tables. The following SQL script makes sure all tables in the import schema contained unique reports and supporting tables only.
Remove duplicates from substance
CREATE TABLE import.substance_unique AS
SELECT
substance_id,
local_number,
report_type,
report_date,
qualification,
country,
age_group_lower_months,
age_group_upper_months,
age_group_reporter,
is_child_report,
sex,
serious
FROM import.substance GROUP BY
substance_id,
local_number,
report_type,
report_date,
qualification,
country,
age_group_lower_months,
age_group_upper_months,
age_group_reporter,
is_child_report,
sex,
serious;
DROP TABLE import.substance;
ALTER TABLE import.substance_unique
RENAME TO substance;
Remove duplicates from substance_concomitant_drug_list
CREATE TABLE import.substance_concomitant_drug_list_unique AS
SELECT
substance_id,
local_number,
report_date,
drugs,
characteristic,
indication,
action,
duration,
dose,
route
FROM import.substance_concomitant_drug_list GROUP BY
substance_id,
local_number,
report_date,
drugs,
characteristic,
indication,
action,
duration,
dose,
route;
DROP TABLE import.substance_concomitant_drug_list;
ALTER TABLE import.substance_concomitant_drug_list_unique
RENAME TO substance_concomitant_drug_list;
Remove duplicates from substance_subject_drug_list
CREATE TABLE import.substance_subject_drug_list_unique AS
SELECT
substance_id,
local_number,
report_date,
drugs,
characteristic,
indication,
action,
duration,
dose,
route
FROM import.substance_subject_drug_list GROUP BY
substance_id,
local_number,
report_date,
drugs,
characteristic,
indication,
action,
duration,
dose,
route;
DROP TABLE import.substance_subject_drug_list;
ALTER TABLE import.substance_subject_drug_list_unique
RENAME TO substance_subject_drug_list;
Remove duplicates from substance_reaction
CREATE TABLE import.substance_reaction_unique AS
SELECT
substance_id,
local_number,
report_date,
reaction,
duration,
outcome,
seriousness_criteria
FROM import.substance_reaction GROUP BY
substance_id,
local_number,
report_date,
reaction,
duration,
outcome,
seriousness_criteria;
DROP TABLE import.substance_reaction;
ALTER TABLE import.substance_reaction_unique RENAME TO substance_reaction;
Remove duplicates from substance_literature
CREATE TABLE import.substance_literature_unique AS
SELECT
substance_id,
local_number,
report_date,
literature
FROM import.substance_literature GROUP BY
substance_id,
local_number,
report_date,
literature;
DROP TABLE import.substance_literature;
ALTER TABLE import.substance_literature_unique RENAME TO substance_literature;
Remove duplicates from product
CREATE TABLE import.product_unique AS
SELECT
product_id,
local_number,
report_type,
report_date,
qualification,
country,
age_group_lower_months,
age_group_upper_months,
age_group_reporter,
is_child_report,
sex,
serious
FROM import.product GROUP BY
product_id,
local_number,
report_type,
report_date,
qualification,
country,
age_group_lower_months,
age_group_upper_months,
age_group_reporter,
is_child_report,
sex,
serious;
DROP TABLE import.product;
ALTER TABLE import.product_unique RENAME TO product;
Remove duplicates from product_concomitant_drug_list
CREATE TABLE import.product_concomitant_drug_list_unique AS
SELECT
product_id,
local_number,
report_date,
drugs,
characteristic,
indication,
action,
duration,
dose,
route
FROM import.product_concomitant_drug_list GROUP BY
product_id,
local_number,
report_date,
drugs,
characteristic,
indication,
action,
duration,
dose,
route;
DROP TABLE import.product_concomitant_drug_list;
ALTER TABLE import.product_concomitant_drug_list_unique
RENAME TO product_concomitant_drug_list;
Remove duplicates from product_subject_drug_list
CREATE TABLE import.product_subject_drug_list_unique AS
select
product_id,
local_number,
report_date,
drugs,
characteristic,
indication,
action,
duration,
dose,
route
FROM import.product_subject_drug_list GROUP BY
product_id,
local_number,
report_date,
drugs,
characteristic,
indication,
action,
duration,
dose,
route;
DROP TABLE import.product_subject_drug_list;
ALTER TABLE import.product_subject_drug_list_unique
RENAME TO product_subject_drug_list;
Remove duplicates from product_reaction
CREATE TABLE import.product_reaction_unique AS
SELECT
product_id,
local_number,
report_date,
reaction,
duration,
outcome,
seriousness_criteria
FROM import.product_reaction GROUP BY
product_id,
local_number,
report_date,
reaction,
duration,
outcome,
seriousness_criteria;
DROP TABLE import.product_reaction;
ALTER TABLE import.product_reaction_unique RENAME TO product_reaction;
Remove duplicates from product_literature
CREATE TABLE import.product_literature_unique AS
SELECT
product_id,
local_number,
report_date,
literature
FROM import.product_literature GROUP BY
product_id,
local_number,
report_date,
literature;
DROP TABLE import.product_literature;
ALTER TABLE import.product_literature_unique RENAME TO product_literature;