From de8868cba19ed65e157d49c6b0d8bccfb5c6c38d Mon Sep 17 00:00:00 2001 From: Moritz Bunkus Date: Mon, 17 Oct 2011 13:38:31 +0200 Subject: [PATCH] =?utf8?q?Constraints=20auf=20vendor/customer:=20Vorher=20?= =?utf8?q?alle=20verwaisten=20Eintr=C3=A4ge=20entfernen?= MIME-Version: 1.0 Content-Type: text/plain; charset=utf8 Content-Transfer-Encoding: 8bit --- sql/Pg-upgrade2/customer_add_constraints.sql | 6 ++++-- sql/Pg-upgrade2/vendor_add_constraints.sql | 7 ++++--- 2 files changed, 8 insertions(+), 5 deletions(-) diff --git a/sql/Pg-upgrade2/customer_add_constraints.sql b/sql/Pg-upgrade2/customer_add_constraints.sql index 89ca9d628..65d338645 100644 --- a/sql/Pg-upgrade2/customer_add_constraints.sql +++ b/sql/Pg-upgrade2/customer_add_constraints.sql @@ -4,8 +4,10 @@ -- @charset: utf-8 -- @ignore: 0 --- verwaiste Zahlungsbedingungen vorher entfernen: -update customer set payment_id = NULL where payment_id not in (select id from payment_terms); +-- verwaiste Einträge vorher entfernen +UPDATE customer SET payment_id = NULL WHERE payment_id NOT IN (SELECT id FROM payment_terms); +UPDATE customer SET language_id = NULL WHERE language_id NOT IN (SELECT id FROM language); +UPDATE customer SET business_id = NULL WHERE business_id NOT IN (SELECT id FROM business); ALTER TABLE customer ADD FOREIGN KEY (payment_id) REFERENCES payment_terms (id); ALTER TABLE customer ADD FOREIGN KEY (language_id) REFERENCES language (id); diff --git a/sql/Pg-upgrade2/vendor_add_constraints.sql b/sql/Pg-upgrade2/vendor_add_constraints.sql index 3d3a81657..4439377b3 100644 --- a/sql/Pg-upgrade2/vendor_add_constraints.sql +++ b/sql/Pg-upgrade2/vendor_add_constraints.sql @@ -4,9 +4,10 @@ -- @charset: utf-8 -- @ignore: 0 --- verwaiste Zahlungsbedingungen vorher entfernen -update vendor set payment_id = NULL where payment_id not in (select id from payment_terms); - +-- verwaiste Einträge vorher entfernen +UPDATE vendor SET payment_id = NULL WHERE payment_id NOT IN (SELECT id FROM payment_terms); +UPDATE vendor SET language_id = NULL WHERE language_id NOT IN (SELECT id FROM language); +UPDATE vendor SET business_id = NULL WHERE business_id NOT IN (SELECT id FROM business); ALTER TABLE vendor ADD FOREIGN KEY (payment_id) REFERENCES payment_terms (id); ALTER TABLE vendor ADD FOREIGN KEY (language_id) REFERENCES language (id); -- 2.20.1