Revision 11982
Added by Aaron Marcuse-Kubitza about 11 years ago
| trunk/inputs/SALVIAS/salvias_plots.~.clean_up.sql | ||
|---|---|---|
| 3 | 3 |
ALTER TABLE "PlotObservations" RENAME TO "plotObservations"; |
| 4 | 4 |
|
| 5 | 5 |
-- Delete rows that do not satisfy foreign key constraints |
| 6 |
-- these must happen in order starting with the outermost table |
|
| 7 |
DELETE FROM "plotObservations" WHERE "PlotID" IN ( |
|
| 8 |
SELECT "plotObservations"."PlotID" |
|
| 9 |
FROM "plotObservations" |
|
| 10 |
LEFT JOIN "plotMetadata" ON "plotMetadata"."PlotID" = "plotObservations"."PlotID" |
|
| 11 |
WHERE "plotMetadata"."PlotID" IS NULL |
|
| 12 |
); |
|
| 6 | 13 |
DELETE FROM stems WHERE "plotobs_id" IN ( |
| 7 |
SELECT stems."plotobs_id"
|
|
| 8 |
FROM stems
|
|
| 9 |
LEFT JOIN "plotObservations" ON "plotObservations"."PlotObsID" = stems."plotobs_id"
|
|
| 10 |
WHERE "plotObservations"."PlotObsID" IS NULL
|
|
| 14 |
SELECT stems."plotobs_id"
|
|
| 15 |
FROM stems
|
|
| 16 |
LEFT JOIN "plotObservations" ON "plotObservations"."PlotObsID" = stems."plotobs_id"
|
|
| 17 |
WHERE "plotObservations"."PlotObsID" IS NULL
|
|
| 11 | 18 |
); |
| 12 | 19 |
|
| 13 | 20 |
-- Enable cascading deletes |
Also available in: Unified diff
fix: inputs/SALVIAS/salvias_plots.~.clean_up.sql: Delete rows that do not satisfy foreign key constraints: also need to do this for plotObservations, since the refreshed data contains dangling rows for that as well