joiner: Multiupdates MySql >= 5.0.12

Ich habe bisher ( leider recht oft ) Updates verwendet, die mit Hilfe eines JOINS eine Tabelle unter Berücksichtigung ALLER Zeile einer anderen ( auch mehrfach ) aktualisiert haben.

Folgendes Beispiel

CREATE TEMPORARY TABLE tmpDst
   ( Id SMALLINT NOT NULL AUTO_INCREMENT PRIMARY KEY,
     Txt TEXT NOT NULL );

CREATE TEMPORARY TABLE tmpSrc
   ( PId SMALLINT NOT NULL,
     Nr SMALLINT NOT NULL,
     Str Text NOT NULL );

INSERT INTO tmpDst
   ( Id, Txt )
VALUES
   ( 1, '' ),
   ( 2, '' );

INSERT INTO tmpSrc
   ( PId, Nr, Str )
VALUES
   ( 1, 1, 'Eins' ),
   ( 2, 2, 'Zwei' ),
   ( 1, 3, 'Drei' ),
   ( 2, 4, 'Vier' ),
   ( 1, 5, 'Fünf' );

Und nu kommts:

UPDATE tmpDst as d
 CROSS JOIN tmpSrc as s
    ON s.PId = d.Id
   SET d.Txt = CONCAT( d.Txt, s.Str )
 WHERE 1;

SELECT * FROM tmpDst;

hat demnach folgendes Ergebnis geliefert:

Id  Txt
--------
1   EinsDreiFünf
2   ZweiVier

Seit MySql 5.0.12 verweist MySql in einem Nebensatz Ihrer Ref. nun darauf, dass das nicht sql2003 konform ist und die entsprechenden Abfragen umgeschrieben werden müssen.

Ergebnis ab 5.0.12:
1   Eins
2   Zwei
fertig, toll

Gibt's eine Möglichkeit, den verblödeten Optimizer davon abzuhalten, die restlichen Zeile außen vor zu lassen?

Hat da schon Jemand eine Lösung?

  1. echo $begrüßung;

    UPDATE tmpDst as d
    CROSS JOIN tmpSrc as s
        ON s.PId = d.Id
       SET d.Txt = CONCAT( d.Txt, s.Str )
    WHERE 1;

    Seit MySql 5.0.12 verweist MySql in einem Nebensatz Ihrer Ref. nun darauf, dass das nicht sql2003 konform ist und die entsprechenden Abfragen umgeschrieben werden müssen.

    Schon wäre es gewesen, wenn du die Stelle verlinkt hättest, denn so habe ich diese Aussage nicht finden können. Und da meist recht viel Text auf einer Handbuchseite steht, empfiehlt es sich zumindest den Anfang des betreffenden Satzes zu zitieren.

    Stattdessen fand ich die Aussage, dass in einem Multiple-Table-Update kein ORDER BY verwendet werden kann[1] und die Reihenfolge der Zuweisungen somit beliebig sein kann[2]. Du bekämst also mit deinem Statement unvorhersagbare Ergebnisse.

    http://dev.mysql.com/doc/refman/5.0/en/update.html:
    [1] "... you cannot use ORDER BY or LIMIT with a multiple-table UPDATE."
    [2] "For multiple-table updates, there is no guarantee that assignments are carried out in any particular order."[3]
    [3] Es kann sein, dass ich diesen Satz missverstanden habe, und er nicht auf diese Situation anwendbar ist.

    Hat da schon Jemand eine Lösung?

    GROUP_CONCAT() zusammen mit einem Subselect kann helfen.

    UPDATE tmpDst as d
     CROSS JOIN
      (SELECT PId, GROUP_CONCAT(Str ORDER BY Nr SEPARATOR '') gc FROM tmpSrc GROUP BY PId) as s
     ON s.PId = d.Id
    SET d.Txt = s.gc;

    Das brachte mir zumindest das gewünschte Ergebnis.

    echo "$verabschiedung $name";

    1. echo $begrüßung;

      echo begruessung();

      UPDATE tmpDst as d
      CROSS JOIN tmpSrc as s
          ON s.PId = d.Id
         SET d.Txt = CONCAT( d.Txt, s.Str )
      WHERE 1;

      Seit MySql 5.0.12 verweist MySql in einem Nebensatz Ihrer Ref. nun darauf, dass das nicht sql2003 konform ist und die entsprechenden Abfragen umgeschrieben werden müssen.

      Schon wäre es gewesen, wenn du die Stelle verlinkt hättest, denn so habe ich diese Aussage nicht finden können. Und da meist recht viel Text auf einer Handbuchseite steht, empfiehlt es sich zumindest den Anfang des betreffenden Satzes zu zitieren.

      sorry: http://dev.mysql.com/doc/refman/5.1/de/join.html
      strgf, "5.0.12", in der Mitte etwa

      Stattdessen fand ich die Aussage, dass in einem Multiple-Table-Update kein ORDER BY verwendet werden kann[1] und die Reihenfolge der Zuweisungen somit beliebig sein kann[2]. Du bekämst also mit deinem Statement unvorhersagbare Ergebnisse.

      http://dev.mysql.com/doc/refman/5.0/en/update.html:
      [1] "... you cannot use ORDER BY or LIMIT with a multiple-table UPDATE."
      [2] "For multiple-table updates, there is no guarantee that assignments are carried out in any particular order."[3]
      [3] Es kann sein, dass ich diesen Satz missverstanden habe, und er nicht auf diese Situation anwendbar ist.

      Die Reihenfolge ist Irrelevant. Viel Schlimmer noch ist, dass das auch bei Berechnungnen schiefgeht ( also zb. a.xx = a.xx + b.yy ). Hier ist die Reihenfolge bei einer Summe sowieso egal.

      Hat da schon Jemand eine Lösung?

      GROUP_CONCAT() zusammen mit einem Subselect kann helfen.

      UPDATE tmpDst as d
      CROSS JOIN
        (SELECT PId, GROUP_CONCAT(Str ORDER BY Nr SEPARATOR '') gc FROM tmpSrc GROUP BY PId) as s
      ON s.PId = d.Id
      SET d.Txt = s.gc;

      Schon getestet ..
      GROUP_CONCAT funktioniert nur bei relativ kurzen Strings zuverlässig, danach fehlt die Hälfte, warum auch immer

      Das brachte mir zumindest das gewünschte Ergebnis.

      Die Frage ist auch, was machen bei komplizierteren Updates ( Berechnungen über die entsprechenden Spalten ) wenn der immer nur eine von der 2. Tabelle verwendet (Reihenfolge hab ich auch schon geändert).

      echo "$verabschiedung $name";

      veranschiedung($actUser["Name"]);

      1. echo $begrüßung;

        Die Reihenfolge ist Irrelevant. Viel Schlimmer noch ist, dass das auch bei Berechnungnen schiefgeht ( also zb. a.xx = a.xx + b.yy ). Hier ist die Reihenfolge bei einer Summe sowieso egal.

        Mein Gedächtnis erinnert sich noch an eine Aussage, dass bei einem Update die ursprünglichen Werte nur einmal gelesen werden und bei mehrfachem Zugriff immer wieder die alten und nicht die neu berechneten Werte herangezogen werden, bzw. dass beim mehrfachen Lesen immer wieder der eingetragene Tabellenwert genommen wird, der Schreibvorgang das Ergebnis aber erst am Ende aktualisiert.

        Es erinnert sich aber auch, dass beim Update eine Zeile nur jeweils einmal berücksichtigt wird und nicht mehrfach.

        Leider fand ich keine Handbuchaussage darüber. Falls das jemand genauer weiß, bzw. die Handbuchstelle findet, würde ich das gern nochmal nachlesen.

        Wenn ich mir dein Ergebnis ansehe, spricht das für meine zweite Erinnerung, sonst hättest du jeweils den letzten Wert als Ergebnis bekommen.

        GROUP_CONCAT() zusammen mit einem Subselect kann helfen.
        GROUP_CONCAT funktioniert nur bei relativ kurzen Strings zuverlässig, danach fehlt die Hälfte, warum auch immer

        Dass das Ergebnis von GROUP_CONCAT() längenbeschränkt ist, und wie das umgangen werden kann, weiß das Handbuch in der Beschreibung zur Funktion zu berichten.

        echo "$verabschiedung $name";