Daten in kommasepariertem Feld mit REPLACE richtig ersetzen

[MichaS.]

Ich lerne gern, habe es aber nach einer Woche suchen und Lernen aufgegeben, allein die richtige Lösung zu finden.
Wer kann mir den richtigen Tipp geben, wie es voll funktioniert bzw. was mache ich falsch?

Die Herausforderung:
Ich habe in meiner mySQL-Datenbank zwei Tabellen, in der Tabelle 1 (t1) sind verschiedene Förderregionen der EU, also z.B. INTERREG-Region Ostseeraum (mit 11 Ostsee-Anreinerstaaten und Teilregionen) als Vollname und kommasepariert in einem Feld "eligible_areas".
Da sollen aber nach erfolgreicher Operation stattdessen nicht die Klarnamen stehen, sondern deren IDs stehen.

Die IDs sind in einer zweiten Tabelle (t2) der 1844 Einzelregionen (also Landkreise, Bezirke, Wojwodschaften, Kantone und Hauptstädte), dort allerdings hat jede einzelne Region ihre eigene Zeile.

Mit welchem Code tausche ich in t1 die kommaseparierten Klarnamen mit deren IDs aus t2 aus, so dass statt "Berlin, Mailand, Paris" dann deren IDs like "18, 25, 77" als kommaseparierte Daten in der ursprünglichen Spalte wieder kommasepariert enthalten sind?


Natürlich habe ich mich belesen und eine Woche mit phpmyadmin viele sql-Varianten probiert und bin ja stolz, dass ich Teilerfolge dahingehend erzielt habe, dass das Replacen mit einem Wert schon einmal funktioniert. Weiter komme ich ohne Hilfe nun seit zwei Tagen nicht mehr. Zeit, die Gemeinschaft um Hilfe anzusuchen.


Das sind meine geistigen Ergebnisse für den Wechsel eines Wertes:

UPDATE `programs_interreg_work` AS t1
INNER JOIN regions_eu_members_sich9 AS t2
ON t1.eligible_area = t2.name_lat
SET `eligible_area` = REPLACE(`eligible_area`,
t2.name_lat,
t2.id)

oder auch die Variante:

UPDATE `programs_interreg_work` AS t1
INNER JOIN regions_eu_members_sich9 AS t2
SET `eligible_area` = REPLACE(`eligible_area`,
t2.name_lat,
t2.id)
WHERE LOCATE(name_lat, eligible_area)


Ich würde mich freuen, wenn mir jemand die Arbeit der einzelnen händischen Anpassung erspart und den entscheidenden Tipp beisteuert.

Kleines extra Bonbon. Leider habe ich festgestellt, dass manchmal die Klarnamen zwischen beiden Tabellen doch etwas voneinander abweichen. Gibt es eine Funktion, die da etwas toleranter ist so wie LIKE %.

Manchmal steht Deutschland in t1 und DEUTSCHLAND in t2. Ich habe das mal versucht durch LOWER (siehe nachfolgend), will aber nicht immer (nur manchmal) funktionieren:

UPDATE `programs_interreg_work` AS t1
INNER JOIN regions_eu_members_sich9 AS t2
SET `eligible_area` = REPLACE(`eligible_area`, t2.name_lat, t2.id)
WHERE LOWER(name_lat) LIKE LOWER('Wałbrzyski')


soweit für den Moment und in froher Erwartung

MichaS. ;-)

 

[akretschmer]

generell: man speichert mehrere Werte nicht Komma-separiert. Das macht von der Falschheit Deiner Lösung keinen Unterschied, ob da Klarnamen oder IDs steheh - Du kannst auch die IDs NICHT als Foreign Key verwenden.

Mein Tipp: wirf das weg, das ist Müll. Fange neu an, mache es richtig.

 

[MichaS.]

generell: man speichert mehrere Werte nicht Komma-separiert. Das macht von der Falschheit Deiner Lösung keinen Unterschied, ob da Klarnamen oder IDs steheh - Du kannst auch die IDs NICHT als Foreign Key verwenden.

Mein Tipp: wirf das weg, das ist Müll. Fange neu an, mache es richtig.

Danke für den guten Hinweis. Ich schaue mal, was da geht. Letztlich bin ich nicht der Programmierer, sondern nur Hobby-Progger und bastle etwas. Hier habe ich ein vorgefertigtes Gerüst und werde wahrscheinlich jetzt gar nicht so viel für den Moment verändern können. Du meinst eher über eine weitere Hilfstabelle, in der dann die Relationen einzeln aufgelistet sind?

 

[akretschmer]

ja, so richtig schon mit Foreign Keys etc.

 

[MichaS.]

ja, so richtig schon mit Foreign Keys etc.

Danke, ich trage das an unseren Progger heran.

Unbesehen davon, gibt es eine Lösung zu meinem Thema bzw. wer weiß, wie das geht?

 

[akretschmer]

easy...

test=*# select * from t1;
 id | orte  
----+-------
  1 | 1,3,5
(1 row)

test=*# select * from t2;
 id |    ort   
----+-----------
  1 | berlin
  2 | dresden
  3 | magdeburg
  4 | riesa
  5 | meißen
  6 | hamburg
(6 rows)
test=*# with x as (select string_agg(ort,', ') from t2 where id in (select regexp_split_to_table(orte,',')::int from t1 where id = 1)) update t1 set orte = x.string_agg from x where id = 1;
UPDATE 1
test=*# select * from t1;
 id |           orte           
----+---------------------------
  1 | berlin, magdeburg, meißen
(1 row)

mal so als Ansatz ...

 

[MichaS.]

easy...

test=*# select * from t1;
 id | orte
----+-------
  1 | 1,3,5
(1 row)

test=*# select * from t2;
 id |    ort
----+-----------
  1 | berlin
  2 | dresden
  3 | magdeburg
  4 | riesa
  5 | meißen
  6 | hamburg
(6 rows)
test=*# with x as (select string_agg(ort,', ') from t2 where id in (select regexp_split_to_table(orte,',')::int from t1 where id = 1)) update t1 set orte = x.string_agg from x where id = 1;
UPDATE 1
test=*# select * from t1;
 id |           orte        
----+---------------------------
  1 | berlin, magdeburg, meißen
(1 row)

mal so als Ansatz ...

Danke mein Guter ;-)

wobei t1 ja eben keine IDs, sondern Klarnamen hat. Aber das kann ich sicherlich in Deiner Formel berücksichtigen.
Deine where id = 1 bedeutet doch, dass dies nur für einen Datensatz mit id = 1 gemacht wird, korrekt?

Zumal ich hier aber jeweils bei t1 und t2 das feld id habe und sicherlich nach meinem Leseverständnis dann noch sagen müsste, welche id von welchem tx. Ich glaube myphpadmin wird sonst meckern oder?

Ansonsten ist mir die Formel zu hoch, da steige ich dann schon als Laie aus ;-(

Aber ich teste das mal heute Abend!

 

[akretschmer]

wobei t1 ja eben keine IDs, sondern Klarnamen hat. Aber das kann ich sicherlich in Deiner Formel berücksichtigen.

ah ja, das hab ich verdreht, aber die Anpassung überlasse ich Dir als Übung ...

 

[MichaS.]

ich habe das ganze mal etwas strukturiert um es irgendwie halbweg zu verstehen und natürlich mit meinen Feldname angepasst, wobei

• eligible_area -- das Datenfeld mit den ursprünglichen kommaseparierte Klarnamen in der t1 ist, die durch ihre IDs ersetzt werden sollen
• name_lat -- der gleiche Name als referenz in der t2, wo auch die dazugehörige id herkommt

Leider kommen jetzt aus dem sql befehlszeilentool von phpmyadmin einige Fehlermeldungen, die ich selbst nicht nachvollziehen kann.

Sie hier:

Statische Analyse:

4 Fehler wurden während der Analyse gefunden.

1. Variablenname wurde erwartet. (near ":" at position 140)
2. Unerwarteter Statement-Anfang. (near "test" at position 0)
3. Unerwartetes Zeichen. (near ")" at position 193)
4. Eine neue Anweisung wurde gefunden, aber kein Trennzeichen zwischen ihm und dem vorhergehenden. (near "update" at position 201)

SQL-Befehl: Kopieren

test = *# with x as ( select string_agg(name_lat,', ') from t2 where id in ( select regexp_split_to_table(`eligible_area`,',')::int from t1 where id = 1 ) ) update t1 set `eligible_area` = x.string_agg from x where id = 1

MySQL meldet:

#1064 - Fehler in der SQL-Syntax. Bitte die korrekte Syntax im Handbuch nachschlagen bei 'test = *# with x as (
select string_agg(name_lat,', ')
from t2
where id i' in Zeile 1

 

[akretschmer]

nun - ich verwende PostgreSQL ... MySQL ist noch nicht so weit. WITH-Abfragen z.B. kann es nicht, Funktionen wie regexp_split_to_table kann es nicht, ...

Ach ja, das "test = *# " ist der Prompt der Datenbank, das ist NICHT bestandteil des SQLs.

 

[MichaS.]

na ich schrieb doch als Einführung das ich mysql als Datenbank habe.
Das heisst, ich kann den Code doch gar nicht anwenden.

Na mal schauen, ob noch jemand schreibt.

Danke für Deine Bemühungen. ;-)

 

[akretschmer]

Daher schrieb ich auch: "Mein Tipp: wirf das weg, das ist Müll. Fange neu an, mache es richtig.", aber Du wolltest ja dennoch eine Lösung ;-)

 

[MichaS.]

Daher schrieb ich auch: "Mein Tipp: wirf das weg, das ist Müll. Fange neu an, mache es richtig.", aber Du wolltest ja dennoch eine Lösung ;-)

Ja, das stimmt. ich wollte eine Lösung für mein Problem.

Vielleicht hat die ja doch noch jemand?