hA3gukG1

1CREATE TABLE IF NOT EXISTS AV_Ort
2(Id INTEGER NOT NULL PRIMARY KEY AUTO_INCREMENT,
3Postleitzahl VARCHAR(10) NOT NULL,
4Ortsname VARCHAR(50) NOT NULL
5)
6ENGINE=InnoDB;
7
8INSERT INTO AV_Ort(Postleitzahl, Ortsname)
9SELECT DISTINCT KD_PLZ, KD_Wohnort 
10FROM AV_Kunde;
11
12ALTER TABLE AV_Kunde
13ADD COLUMN KD_Wohnort_FK INTEGER;
14
15ALTER TABLE AV_Kunde
16ADD CONSTRAINT fk_wohnort 
17FOREIGN KEY (KD_Wohnort_FK) 
18REFERENCES AV_Ort(Id);
19
20UPDATE AV_Kunde kunde
21INNER JOIN (AV_Ort ort)
22ON (ort.Postleitzahl = kunde.KD_PLZ)
23SET kunde.KD_Wohnort_FK = ort.Id
24WHERE ort.Ortsname = kunde.KD_Wohnort;
25
26ALTER TABLE AV_Kunde
27DROP COLUMN KD_PLZ,
28DROP COLUMN KD_Wohnort;