Mehrfach Update STatement

[Delphi.Narium]

SQL-Code:

update as400 set
  sequence = case
      when waaunr = '408216' and waaupo = 0 and oaagnr = 10 then 1
      when waaunr = '408217' and waaupo = 0 and oaagnr = 10 then 2
      when waaunr = '408218' and waaupo = 0 and oaagnr = 10 then 3
      else sequence
  end,
  frozen = case
      when waaunr = '408216' and waaupo = 0 and oaagnr = 10 then 1
      when waaunr = '408217' and waaupo = 0 and oaagnr = 10 then 1
      when waaunr = '408218' and waaupo = 0 and oaagnr = 10 then 0
      else frozen
  end,
  plan_date = case
      when waaunr = '408216' and waaupo = 0 and oaagnr = 10 then '2019-07-05'
      when waaunr = '408217' and waaupo = 0 and oaagnr = 10 then '2019-07-05'
      when waaunr = '408218' and waaupo = 0 and oaagnr = 10 then '2019-07-06'
      else plan_date
  end
where waaunr in ('408216', '408217', '408218')

Geht es damit?

Würde aber ggfls. eine kaum überschaubare Menge von Datensätzen verändern, die nicht verändert werden muss.

Aber eigentlich:

Wenn oaagnr unterschiedlich sein kann, dann muss das Update halt eben für jeden der möglichen Werte gemacht werden. Dann reicht halt eben ein "globalgalaktisches" Update für "alles in einem Rutsch" nicht aus.

Und wenn waaupo nicht immer gleich ist, dann muss eben für jede zulässige Kombination aus waaupo, oaagnr und waaunr ein Update gemacht werden.

SQL ist übrigens bei kleinen Datenmengen deutlich schneller, als bei "einmal grob so alles mögliche und dann mal schaun, ob's eventuell doch nicht passt" ;-)
Die Ausführung vieler Updates mit exakten Angaben zu waaupo, oaagnr und waaunr kann daher deutlich schneller sein, als die momentan favorisierte Variante und: Das gerade aktut auftretende Problem muss nicht irgendwie umschifft werden sondern tritt garnicht erst auf und vermeidet ggfls. fälschlich durchgeführte Updates auf Datensätze, die nicht geändert werden müssen. Was z. B. bei meinem obigen Updatestatement der Fall ist. Gibt es da auf der Datenbank irgendwelche Trigger, so werden diese auch ausgeführt, was durchaus kontraproduktiv sein kann, wenn sie z. B. eine Versionierung ... durchführen.

Im konkreten Fall erwarte ich eigentlich dashier:

SQL-Code:

update as400 set sequence = 1, frozen = 1, plan_date = '2019-07-05' where waaunr = '408216' and waaupo = 0 and oaagnr = 10;

update as400 set sequence = 2,  frozen = 1, plan_date = '2019-07-05' where waaunr = '408217' and waaupo = 0 and oaagnr = 10;

update as400 set sequence = 3, frozen = 0, plan_date = '2019-07-06' where waaunr = '408218' and waaupo = 0 and oaagnr = 10;

Man kann SQL-Statements wunderbar parametrisieren:

SQL-Code:

update as400 set sequence = :sequence, frozen = :frozen, plan_date = :plan_date
where waaunr = :waaunr and waaupo = :waaupo and oaagnr = :oaagnr;

und hat damit im Quelltext nur noch ein kurzes und verständliches Updatestatement.

Das ruft man dann für jede erforderliche Wertekombination auf, wobei das im Quelltext vermutlich deutlich einfacher umzusetzen ist, als der Zusammenbau des momentan genutzten Statements.

[jobo]

@Delphi.Narium :thumb:

Damit's nicht ganz so übersichtlich wird, wie Delphi.Narium es vorschlägt noch eine Ergänzung/Frage:
Woher kommen die neuen Daten?
Werden sie eingetippt? Oder "ergeben" sie sich aus anderen, bereits vorhandenen Daten, die mglw. sogar in einer Schleife abgerufen werden, in der dann das Updatestatement gebaut und gestartet wird?

Wenn das der Fall ist, kann man vielleicht auch alle Updateparameter und Join Kriterien auch über ein Select holen und an das Update übergeben.
Stichwort: korreliertes Update
Damit hätte man dann (wieder) ein Massenupdate, das aber seine Quelldaten aus der DB bekommt, also komplett auf der DB abläuft und auch sehr schnell ist.

[Schokohase]

Zitat:

Zitat von Delphi.Narium (Beitrag 1436260)

Man kann SQL-Statements wunderbar parametrisieren:

SQL-Code:

update as400 set sequence = :sequence, frozen = :frozen, plan_date = :plan_date
where waaunr = :waaunr and waaupo = :waaupo and oaagnr = :oaagnr;

und hat damit im Quelltext nur noch ein kurzes und verständliches Updatestatement.

Das ruft man dann für jede erforderliche Wertekombination auf, wobei das im Quelltext vermutlich deutlich einfacher umzusetzen ist, als der Zusammenbau des momentan genutzten Statements.

Und wenn man die Parameter in einem/r Record/Klasse zusammenfasst und einer Methode eine Auflistung davon übergibt, dann wird das alles zum Spaziergang.

Transaktion starten, jedes Item durch das Update-Statement jagen (Parameter setzen und ausführen) und Transaktion comitten.

[Ykcim]

Also die Daten ergeben sich. Der User schiebt per Drag&Drop die Zeilen einer Tabelle, um eine gute Auftragsreihenfolge zu erhalten. Aus der Position des Auftrags werden die Werte errechnet. Sequence zeigt die Reichenfolge an.
Ich erstelle mir das Update-Query in einer Functionanhand der Daten in der Tabelle. Die Daten können von Menge und Inhalt sehr unterschiedlich sein.

Delphi-Quellcode:

function TMySQLDB.Planung_Speichern(SG: TAdvStringGrid): boolean;
var   I: integer;
      where_SQL: string;
begin
   FMyWriteQuery.SQL.Clear;
   FMyWriteQuery.SQL.Add('update as400 set ');
   //Sequence
   FMyWriteQuery.SQL.Add('sequence = case ');
   where_SQL:=' where concat(waaunr,'+QuotedStr('-')+',waaupo,'+QuotedStr('-')+',oaagnr) in (';
   for I := 2 to SG.RowCount -2 do begin       //Filter-Zeile und FloatingFooter
      FMyWriteQuery.SQL.Add(' when concat(waaunr,'+QuotedStr('-')+',waaupo) ='+QuotedStr(SG.Cells[1,I])+
                            ' and oaagnr ='+SG.Cells[5,I]+' then '+SG.Cells[0,I]+' ');
      if I=2 then
         where_SQL:=where_SQL+QuotedStr(SG.Cells[1,I]+'-'+SG.Cells[5,I])
      else
         where_SQL:=where_SQL+', '+QuotedStr(SG.Cells[1,I]+'-'+SG.Cells[5,I]);
   end;
   FMyWriteQuery.SQL.Add(' else 0 end, ');

   //Frozen
   FMyWriteQuery.SQL.Add('frozen = case ');
   for I := 2 to SG.RowCount -2 do begin       //Filter-Zeile und FloatingFooter
      FMyWriteQuery.SQL.Add(' when concat(waaunr,'+QuotedStr('-')+',waaupo) ='+QuotedStr(SG.Cells[1,I])+
                            ' and oaagnr ='+SG.Cells[5,I]+' then '+SG.Cells[25,I]+' ');
   end;
   FMyWriteQuery.SQL.Add(' else 0 end, ');

   //PlanDate
   FMyWriteQuery.SQL.Add('plan_date = case ');
   for I := 2 to SG.RowCount -2 do begin       //Filter-Zeile und FloatingFooter
      FMyWriteQuery.SQL.Add(' when concat(waaunr,'+QuotedStr('-')+',waaupo) ='+QuotedStr(SG.Cells[1,I])+
                            ' and oaagnr ='+SG.Cells[5,I]+' then '+QuotedStr(copy(SG.Cells[14,I],7,4)+'-'+copy(SG.Cells[14,I],4,2)+'-'+copy(SG.Cells[14,I],1,2))+' ');
   end;
   FMyWriteQuery.SQL.Add(' else '+QuotedStr('0000-00-00')+' end ');

   where_SQL:=where_SQL+')';
   FMyWriteQuery.SQL.Add(where_SQL);

   FMyWriteQuery.Execute;
   Result:=true;
end;

Ich habe vorher Einzelupdates gehabt und hatte das Problem, dass das sehr lange dauerte (mehrere Sekunden, obwohl ich nur die ersten 15 Zeile gespeichert habe). Jetzt geht es sehr schnell, sodass ich problemlos mehrere Tabelle auf einmal speichern kann.

Daher bin ich mit dieser Lösung gerade sehr glücklich...

Delphi-Quellcode:

update as400 set
  sequence = case
      when (concat(waaunr,'-',waaupo) ='408216-0' and oaagnr =10) then 1
      when (concat(waaunr,'-',waaupo) ='408217-0' and oaagnr =10) then 2
      when (concat(waaunr,'-',waaupo) ='408218-0' and oaagnr =10) then 3
      else 0
  end,
  frozen = case
      when (concat(waaunr,'-',waaupo) ='408216-0' and oaagnr =10) then 1
      when (concat(waaunr,'-',waaupo) ='408217-0' and oaagnr =10) then 1
      when (concat(waaunr,'-',waaupo) ='408218-0' and oaagnr =10) then 0
      else 0
  end,
  plan_date = case
      when (concat(waaunr,'-',waaupo) ='408216-0' and oaagnr =10) then '2019-07-05'
      when (concat(waaunr,'-',waaupo) ='408217-0' and oaagnr =10) then '2019-07-05'
      when (concat(waaunr,'-',waaupo) ='408218-0' and oaagnr =10) then '2019-07-06'
      else '000-00-00'
  end
where concat(waaunr,'-',waaupo,'-',oaagnr) in ('408216-0-10', '408217-0-10', '408218-0-10')

Zitat:

Wer auf diese Spaltennamen gekommen ist gehört übrigens standrechtlich erschossen.

Spaltenname von XPPS... ;-)

Gruß Patrick

[peterbelow]

Zitat:

Zitat von Ykcim (Beitrag 1436252)

Hallo Zusammen,

oaagnr kann unterschiedlich sein. In diesem aktuellen Beispiel ist es zwar identisch, aber bei nachfolgenden Prozessen ist das nicht sichergestellt. Daher muss ich es als zusätzlichen Parameter verwenden. Und auch waaupo ist zwar meistens 0 aber nicht immer...

Und wenn es unterschiedlich ist, kenne ich nicht die Syntax, es anders unterzubringen...

Gruß
Patrick

Naja, aber Du mußt sicherstellen, dass die WHERE-Klausel wirklich nur die Zeilen findet, die Du updaten willst, sonst hast Du halt das Problem, dass für manche der gefundenen Zeilen keine der When-Klauseln passt und dann halt NULL in die Spalte geschrieben wird.

Mmh, doch, Du könntest für jedes Case eine ELSE-Klausel verwenden, die einfach den aktuellen Wert der Spalte in diese schreibt.

Code:

update as400 set
  sequence = case
      when (concat(waaunr,'-',waaupo) ='408216-0' and oaagnr =10) then 1
      when (concat(waaunr,'-',waaupo) ='408217-0' and oaagnr =10) then 2
      when (concat(waaunr,'-',waaupo) ='408218-0' and oaagnr =10) then 3
      else sequence
  end,
  frozen = case
      when (concat(waaunr,'-',waaupo) ='408216-0' and oaagnr =10) then 1
      when (concat(waaunr,'-',waaupo) ='408217-0' and oaagnr =10) then 1
      when (concat(waaunr,'-',waaupo) ='408218-0' and oaagnr =10) then 0
      else frozen
  end,
  plan_date = case
      when (concat(waaunr,'-',waaupo) ='408216-0' and oaagnr =10) then '2019-07-05'
      when (concat(waaunr,'-',waaupo) ='408217-0' and oaagnr =10) then '2019-07-05'
      when (concat(waaunr,'-',waaupo) ='408218-0' and oaagnr =10) then '2019-07-06'
      else plan_date
  end
where concat(waaunr,'-',waaupo,'-',oaagnr) in ('408216-0-10', '408217-0-10', '408218-0-10')

Probier mal aus, ob die Datenbank-Engine sowas akzeptiert.

[Ykcim]

Zitat:

Naja, aber Du mußt sicherstellen, dass die WHERE-Klausel wirklich nur die Zeilen findet, die Du updaten willst, sonst hast Du halt das Problem, dass für manche der gefundenen Zeilen keine der When-Klauseln passt und dann halt NULL in die Spalte geschrieben wird.

Das stelle ich dadurch sicher, dass waaunr, waaupo und oaagnr als Kombination unique sind und als Primärschlüssel fungieren... Daher behaupte ich, dass mit meinem aktuellen Statement wirklich nur die Zeile upgedatet werden, die auch einen Wert haben. Das ELSE nutze ich nur, weil es sonst einen Fehler in der Syntax gibt...

Gruß Patrick

[Delphi.Narium]

Case when funktioniert auch ohne Else und zwar ohne Syntaxfehler. Welchen Syntaxfehler erhältst Du denn?

Was mir schleierhaft ist:

SQL-Code:

update as400 set
  sequence = case
      when (concat(waaunr,'-',waaupo) ='408216-0' and oaagnr =10) then 1
      when (concat(waaunr,'-',waaupo) ='408217-0' and oaagnr =10) then 2
      when (concat(waaunr,'-',waaupo) ='408218-0' and oaagnr =10) then 3
      else 0
  end,
  frozen = case
      when (concat(waaunr,'-',waaupo) ='408216-0' and oaagnr =10) then 1
      when (concat(waaunr,'-',waaupo) ='408217-0' and oaagnr =10) then 1
      when (concat(waaunr,'-',waaupo) ='408218-0' and oaagnr =10) then 0
      else 0
  end,
  plan_date = case
      when (concat(waaunr,'-',waaupo) ='408216-0' and oaagnr =10) then '2019-07-05'
      when (concat(waaunr,'-',waaupo) ='408217-0' and oaagnr =10) then '2019-07-05'
      when (concat(waaunr,'-',waaupo) ='408218-0' and oaagnr =10) then '2019-07-06'
      else '000-00-00'
  end
where concat(waaunr,'-',waaupo,'-',oaagnr) in ('408216-0-10', '408217-0-10', '408218-0-10')

Im where setzt du 3 Werte per Concat zusammen, im When zwei und den dritten fügst Du per and hinzu.

Warum, wo ist da der sittliche Nährwert? Es sind doch letztlich die gleichen Wertkombinationen abzufragen.

So wäre es dann schon etwas konsequenter:

SQL-Code:

update as400 set
  sequence = case
      when (concat(waaunr,'-',waaupo,'-',oaagnr) = '408216-0-10') then 1
      when (concat(waaunr,'-',waaupo,'-',oaagnr) = '408217-0-10') then 2
      when (concat(waaunr,'-',waaupo,'-',oaagnr) = '408218-0-10') then 3
      else 0
  end,
  frozen = case
      when (concat(waaunr,'-',waaupo,'-',oaagnr) = '408216-0-10') then 1
      when (concat(waaunr,'-',waaupo,'-',oaagnr) = '408217-0-10') then 1
      when (concat(waaunr,'-',waaupo,'-',oaagnr) = '408218-0-10') then 0
      else 0
  end,
  plan_date = case
      when (concat(waaunr,'-',waaupo,'-',oaagnr) ='408216-0-10') then '2019-07-05'
      when (concat(waaunr,'-',waaupo,'-',oaagnr) ='408217-0-10') then '2019-07-05'
      when (concat(waaunr,'-',waaupo,'-',oaagnr) ='408218-0-10') then '2019-07-06'
      else '000-00-00'
  end
where concat(waaunr,'-',waaupo,'-',oaagnr) in ('408216-0-10', '408217-0-10', '408218-0-10')

Oder auch so:

SQL-Code:

update as400 set
  sequence = case
      when waaunr = '408216' and waaupo = 0 and oaagnr = 10 then 1
      when waaunr = '408217' and waaupo = 0 and oaagnr = 10 then 2
      when waaunr = '408218' and waaupo = 0 and oaagnr = 10 then 3
      else 0
  end,
  frozen = case
      when waaunr = '408216' and waaupo = 0 and oaagnr = 10 then 1
      when waaunr = '408217' and waaupo = 0 and oaagnr = 10 then 1
      when waaunr = '408218' and waaupo = 0 and oaagnr = 10 then 0
      else 0
  end,
  plan_date = case
      when waaunr = '408216' and waaupo = 0 and oaagnr = 10 then '2019-07-05'
      when waaunr = '408217' and waaupo = 0 and oaagnr = 10 then '2019-07-05'
      when waaunr = '408217' and waaupo = 0 and oaagnr = 10 then '2019-07-06'
      else '0000-00-00'
  end
where waaunr in ('408216', '408217', '408218') and waaupo = 0 and oaagnr = 10

Die von Dir bisher gewählte Variante hätte ich früher höchstwahrscheinlich nicht durch den Test und die Qualitätssicherung bekommen, da bei ihr potenziell Datensätze geändert werden könnten, die nicht geändert werden dürfen. Und genau diese Fehlermöglichkeit war ja überhaupt erst der Grund, weshalb Du diesen Thread eröffnet hast.

[Ykcim]

Also die erste Lösung würde funktionieren, aber die Möglichkeit der 3 Werte mit concat zusammen zu fassen hatte ich erst später. Aber das könnte ich ändern.
Deine zweite Lösung funktioniert nicht, da die Kombination AuftragsNr (waaunr) dem Zusatz (waaupo) und der ArbeitsgangNr (oaagnr) bei jedem Auftrag eine andere sein kann. Je nachdem bei welchem AG man gerade ist und es ist möglich, dass Aufträge mit einer unterschiedlichen ArbeitsgangNr auf der selben Maschine laufen und dann beim Speichern in der selben Tabelle stehen...

Die Variante die ich aktuell nutze schließt es meines Erachtens aus, dass Datensätze geändert werden, die nicht geändert werden sollen.

Zitat:

Case when funktioniert auch ohne Else und zwar ohne Syntaxfehler. Welchen Syntaxfehler erhältst Du denn?

Ich werde das Thema ohne ELS noch einemal testen. Ich denke, dass die Tatsache, dass ungewollt andere Datensätze geändert wurden, für die kein Wert in der Where-Klausel vorhanden waren, zu dem Fehler führten. In der letzten Where-Klausel hatte ich ja waaunr und waaupo abgefragt. Die Kombination ist ja für alle Arbeitsgänge des Auftrags dieselbe. Nur wenn man die ArbeitsgangNr (oaagnr) auch noch abfragt, ist eine Eindeutigkeit garantiert... Daher habe ich oaagnr mit in das concat eingebunden...

Gruß
Patrick

[p80286]

Zitat:

Zitat von Delphi.Narium (Beitrag 1436287)

Die von Dir bisher gewählte Variante hätte ich früher höchstwahrscheinlich nicht durch den Test und die Qualitätssicherung bekommen, da bei ihr potenziell Datensätze geändert werden könnten, die nicht geändert werden dürfen. Und genau diese Fehlermöglichkeit war ja überhaupt erst der Grund, weshalb Du diesen Thread eröffnet hast.

:thumb:
Wobei ich vom Verständnis her mit

Code:

 else 0

ein Problem habe. Ich würde ein

SQL-Code:

else Sequence
....
else frozen
....
else plan_date

bevorzugen, falls das syntaktisch möglich ist, oder noch besser, im else-Zweig gar nichts tun.

Gruß
K-H

[peterbelow]

[QUOTE=p80286;1436333]

Zitat:

Zitat von Delphi.Narium (Beitrag 1436287)

... falls das syntaktisch möglich ist, oder noch besser, im else-Zweig gar nichts tun.

Gruß
K-H

"gar nichts tun" ist halt nicht. Wenn keine der when-Klauseln zutrifft ist das Resultat des Case-Statements NULL.