-- CREATE TABLE grPLZ_20200504 AS SELECT * FROM grplz; 
-- CREATE TABLE ofadressen_20200504 AS SELECT * FROM ofadressen; 
-- CREATE TABLE `ofadressen-weitere_20200504` AS SELECT * FROM `ofadressen-weitere`; 


-- DROP TABLE tmp_duplPLZ;
    
-- CREATE TABLE tmp_duplPLZ AS 
    SELECT  Land, Plz, Ort , MIN(idnr) erstePLZ, MAX(idnr) duplukatPLZ , COUNT(*)
    FROM grplz 
    GROUP BY Land, PLz, Ort 
    HAVING COUNT(*) > 1
    ORDER BY Land, Plz, Ort; 
    
    
-- UPDATE ofadressen , tmp_duplPLZ SET ofadressen.PLZ = tmp_duplPLZ.erstePLZ 
	 WHERE  ofadressen.PLZ = tmp_duplPLZ.duplukatPLZ;
    
    
-- UPDATE `ofadressen-weitere` , tmp_duplPLZ SET `ofadressen-weitere`.PLZ = tmp_duplPLZ.erstePLZ 
	 WHERE  `ofadressen-weitere`.PLZ = tmp_duplPLZ.duplukatPLZ; 
     
    
--  DROP TABLE tmp_grplzneuaufbau;


--  CREATE TABLE tmp_GrPLZNeuAufbau
 AS 
 SELECT distinct PLZ FROM 
 ofadressen 
 UNION 
 SELECT distinct PLZ FROM 
 `ofadressen-weitere`; 
 
 
-- ALTER TABLE `tmp_grplzneuaufbau`
	ADD INDEX `Schlüssel 1` (`PLZ`);

 
-- DELETE FROM grplz WHERE  NOT exists  (SELECT PLZ FROM tmp_GrPLZNeuAufbau na WHERE na.PLZ = IDNR);
 
-- SELECT COUNT(*) FROM grplz;
