Projekt Adressbücher/Qualitätssicherung

SELECT concat('UPDATE entry SET prefix=\'', extensions_elt , '-\' WHERE id=', extensions,';') AS request 
  FROM entry_extensions WHERE extensions_idx = 46;
SELECT concat('UPDATE entry SET page=\'', extensions_elt , '\' WHERE id=', extensions,';') AS request 
  FROM entry_extensions WHERE extensions_idx = 80;
 
UPDATE entry SET lastname = concat( substring(lastname,8), ', von der')  WHERE lastname LIKE 'von der %';
UPDATE entry SET lastname = concat( substring(lastname,4), ', von')  WHERE lastname LIKE 'von %';
UPDATE entry SET lastname = concat( substring(lastname,3), ', de')  WHERE lastname LIKE 'de %';
UPDATE entry SET lastname= concat( substring(lastname,4), ', von')  WHERE lastname LIKE 'v. %';
UPDATE entry SET lastname=concat( substring(lastname,14), ', Freiherr von')   WHERE lastname LIKE 'Freiherr von %';
UPDATE entry SET lastname=trim(lastname) WHERE lastname LIKE ' %';
 
UPDATE search_entry SET lastname = concat( substring(lastname,8), ', von der')  WHERE lastname LIKE 'von der %';
UPDATE search_entry SET lastname = concat( substring(lastname,4), ', von')  WHERE lastname LIKE 'von %';
UPDATE search_entry SET lastname = concat( substring(lastname,3), ', de')  WHERE lastname LIKE 'de %';
UPDATE search_entry SET lastname= concat( substring(lastname,4), ', von')  WHERE lastname LIKE 'v. %';
UPDATE search_entry SET lastname=concat( substring(lastname,14), ', Freiherr von')   WHERE lastname LIKE 'Freiherr von %';
UPDATE search_entry SET lastname=trim(lastname) WHERE lastname LIKE ' %';
 
UPDATE entry SET lastname = substring_index(lastname, ', geb.', 1), birthname= substring_index(lastname, ', geb. ', -1) 
 WHERE lastname LIKE '%, geb.%' AND NOT lastname LIKE '%(%';
INSERT INTO search_entry SELECT NULL, version, address,book_id,entry_id,family_status,firstname, substring_index(lastname, ', geb. ', -1), occupation,place,place_id,sort_address,title,year FROM search_entry
 WHERE lastname LIKE '%, geb.%' AND NOT lastname LIKE '%(%';
 
UPDATE entry SET family_status ='Witwe', occupation = NULL WHERE occupation ='Witwe' AND family_status IS NULL;
UPDATE search_entry SET family_status ='Witwe', occupation = NULL WHERE occupation ='Witwe' AND family_status IS NULL;
 
UPDATE entry SET street = NULL WHERE street ='-';

SELECT place.name AS place, place.gov_id,  ifnull(prefix,'') AS prefix,ifnull(street,'') AS street,count(*) FROM entry, place WHERE book_id=208 AND place_id=place.id GROUP BY place.name, prefix, street;