Wie Datensatz beim Erstellen mit Daten aus einer Unterabfrage ergänzen?

Apotessar

Ich versuche gerade eine Stoffdatenbank umzusetzen. Eine Abfrage macht mir dabei Probleme.
Es gibt zwei Tabellen, Artikel und Stoffdaten:

tab_artikel
-artikel_id
-artikel_name
-stoffeigenschaft_a
-cas_nr

tab_stoffdaten
-sd_id
-stoffeigenschaft_a
-stoffeigenschaft_b
-cas_nr

Beim Erfassen besteht die Möglichkeit, dass Werte bereits vorhanden sind. Diese stammen aus öffentlich verfügbaren Quellen und sind in der Tabelle Stoffdaten gespeichert. In dieser Tabelle sind sd_id und cas_nr eindeutig.

Mein aktueller Ansatz:
Backend Funktion über Trigger (INSERT, UPDATE)

UPDATE tab_artikel
INNER JOIN tab_stoffdaten
ON tab_artikel.cas_nr= tab_stoffdaten.cas_nr
SET tab_artikel.stoffeigenschaft_a= tab_stoffdaten.stoffeigenschaft_a
WHERE "aktueller datensatz"?

Das erzeugt leider eine Fehlermeldung bzgl. eines Ausdrucks in der Nähe von INNER. Zudem ist mir nicht klar, wie ich in diesem Kontext nur den aktuellen Datensatz anspreche.
Vielleicht kann mir jemand mit einem Rat auf die Sprünge helfen.
Besten Dank
Pascal

BTW: Falls jemand den Titel zu: "Wie Datensatz beim Erstellen mit Daten aus einer Unterabfrage ergänzen?" ändern könnte. Wie ich gerade sehe, ist das nachträglich nicht mehr möglich. Danke

lsw-fabian

Hi @Apotessar,
wenn du in deinen Backend Triggern mit den Daten des aktuellen Datensatzes arbeiten willst, muss du NEW. + die aktuellen Platzhalter verwenden.

Für das UPDATE brauchst du keinen INNER JOIN, deine Anfrage sollte also in etwa so aussehen:

UPDATE {appname}.[tab_artikel]
SET (appname.tab_artikel.tab_artikel.stoffeigenschaft_a) = NEW.(appname.tab_stoffdaten.tab_artikel.stoffeigenschaft_a)
WHERE (appname.tab_artikel.tab_artikel.cas_nr) = NEW.(appname.tab_stoffdaten.tab_artikelcas_nr);

Das funktioniert aber nur, wenn artikelcas_nr auf beiden Seiten immer identisch ist. Darf ich fragen, warum die Werte auf 2 Relationen gleichtzeitig pflegst? Wenn ich es richtig verstanden habe, besteht dein Artikel aus Stoffdaten - wenn diese so aufeinander aufbauen, wäre eine n:1 Relation zwischen Artikel und Stoffdaten vielleicht der einfachere Weg?

Apotessar

lsw-fabian
Danke für deine Antwort. Werde ich nachher mal ausprobieren.

Zur Frage, die Daten pflege ich nur in einer Relation. Ich habe aus externen Quellen Daten in eine separate Relation eingelesen. Die Daten stammen aus Stoffdatenbanken und enthalten alle möglichen Verbindungen. Ziel ist, dass nur die Daten von Substanzen in die gepflegte Relation übernommen werden, die auch aktiv eingepflegt worden sind. Die zweite Relation dient damit nur als Hilfsmittel, falls die Daten schon vorhanden wären. Einen grossen Teil der Daten wird sich in der externen Datenbank nicht finden lassen. Daher sehe ich gerade davon ab, die externe Datenbank direkt über die cas_nr zu verknüpfen.

Apotessar

So, ich habe den Vorschlag jetzt mal ausprobiert und damit ein wenig herumgespielt. Dieser liefert in meiner Situation zwar keine Fehlermeldung oder Log Einträge, aber leider auch keine Daten.
Ebenfalls habe ich versucht das Problem mit einem Minimalbeispiel nachzustellen:

Link läuft am 15.08.2023 ab.
https://www.swisstransfer.com/d/008a846a-9eca-490d-aa06-ca3de19f88c1

Konkret soll in diesem Beispiel eine beliebige Eigenschaft aus Tabelle Daten übernommen werden, wenn in der Tabelle Artikel ein Name erfasst wird, der in der Tabelle Daten schon vorhanden ist.

Tabellen im neuen Beispiel

Artikel (Spalten: id, name, eigenschaft_int, eigenschaft_text, eigenschaft_url)
(leer)

Daten (Spalten: id, name, eigenschaft_int, eigenschaft_text, eigenschaft_url)
1, a, 100, Hundert, http://100.de
1, b, 100, Hundert, http://100.de
1, c, 100, Hundert, http://100.de

Erwartung: Wenn ich in der Relation Artikel einen Eintrag erstelle der nur einen Namen (als Beispiel a) enthält wird eine Eigenschaft aus der Relation Daten über den Namen ergänzt (nicht verknüpft, sondern direkt als Wert gesetzt). Die Relation Daten soll zu jedem Zeitpunkt unabhängig bleiben.

Folgende Backend Funktion wurde erstellt:
$BODY$
DECLARE
BEGIN
UPDATE {copycat}.[artikel]
SET (copycat.artikel.eigenschaft_int) = NEW.(copycat.daten.eigenschaft_int)
WHERE (copycat.artikel.name) = NEW.(copycat.daten.name);
RETURN NEW;
END;
$BODY$

Mit der Relation Artikel verknüpft und an INSERT/UPDATE gekoppelt.

Vielleicht noch zur Erklärung des Vorgehens (bezogen auf das erste Beispiel im ersten Post). Ich kann in meiner realen Datentabelle keine Datensätze hinzufügen. Diese wird regelmässig mit neuen Werten befüllt (Daten von Extern). Daher keine 1:n Verknüpfung. Zudem haben nicht alle erfassten Artikel eine CAS-Nummer und können damit nicht in jedem Fall nachgeschlagen werden. Wenn eine CAS Nr. vorhanden ist, ist diese auch mit einer hohen Wahrscheinlichkeit in meiner Datentabelle hinterlegt. In dieser befindet sich ebenfalls die URL zur GESTIS-Stoffdatenbank. Damit wäre es möglich, die Sicherheitshinweise für die Substanz direkt nach der Erfassung des Artikels (mit CAS Nr.) als iFrame anzeigen zu lassen, wenn ein entsprechender Eintrag vorhanden ist.

lsw-fabian

Guten Morgen,

danke, dass du es nochmal erklärt hast, ich habe es erst etwas anders interpretiert.

Damit du die Daten aus "daten" holst, musst du vorher ein SELECT machen, da du einen BEFORE Trigger verwendest, musst du außerdem kein UPDATE verwenden sondern kannst das Attribute direkt anpassen:

$BODY$
DECLARE
	_int integer;
BEGIN
	SELECT (copycat.daten.eigenschaft_int) INTO _int
	FROM {copycat}.[daten]
	WHERE (copycat.daten.name) = NEW.(copycat.artikel.name);

	NEW.(copycat.artikel.eigenschaft_int) := _int;

	RETURN NEW;
END;
$BODY$