Direktes UPDATE mehrerer Datensätze mit UNIQUE key

[Gustaf Mossakowski]

Hallo,

ich habe z. B. eine Tabelle nachrichten_medien mit den Feldern nm_id,
nachricht_id, medium_id und reihenfolge. Darin sind Bilder zu
Nachrichten gespeichert. Auf reihenfolge und nachricht_id liegt ein
UNIQUE key:

CREATE TABLE IF NOT EXISTS `nachrichten_medien` (
`nm_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`nachricht_id` int(10) unsigned NOT NULL DEFAULT '0',
`medium_id` int(10) unsigned NOT NULL DEFAULT '0',
`reihenfolge` smallint(5) unsigned NOT NULL DEFAULT '1',
PRIMARY KEY (`nm_id`),
UNIQUE KEY `nachricht_medium` (`nachricht_id`,`medium_id`),
UNIQUE KEY `reihenfolge` (`reihenfolge`,`nachricht_id`)
) ENGINE=MyISAM;

Die Daten sehen z. B. so aus:

mysql> SELECT nm_id, nachricht_id, medium_id, reihenfolge FROM
nachrichten_medien WHERE nachricht_id = 169;
+-------+--------------+-----------+-------------+
| nm_id | nachricht_id | medium_id | reihenfolge |
+-------+--------------+-----------+-------------+
| 125 | 169 | 1177 | 2 |
| 124 | 169 | 1178 | 1 |
| 126 | 169 | 1179 | 3 |
+-------+--------------+-----------+-------------+
3 rows in set (0.00 sec)

Wie tausche ich am einfachsten den Wert im Feld reihenfolge, z. B. so,
dass bei nm_id 125 reihenfolge = 1 ist, bei nm_id 124 reihenfolge = 2?

So etwas geht nicht:

mysql> UPDATE nachrichten_medien
SET reihenfolge = CASE nm_id
WHEN 125 THEN 1
WHEN 124 THEN 2
END
WHERE nm_id IN (124, 125);
ERROR 1062 (23000): Duplicate entry '2-169' for key 'reihenfolge'

Gibt es da etwas oder geht das nicht direkt?
Viele Gr��e
Gustaf

[Bernd Nawothnig]

Das Feld reihenfolge brauchst Du doch gar nicht in der Tabelle. Das kann
und sollte man besser berechnen:

#v+
SELECT
mm_id,
nachricht_id,
medium_id,
mm_id - (
SELECT
min(mm_id)

FROM
nachrichten_medien
WHERE
nachricht_id = 169

) + 1 AS reihenfolge

FROM
nachrichten_medien
WHERE
nachricht_id = 169
;

#v-

(untested)




Bernd

[Gustaf Mossakowski]

Bernd Nawothnig schrieb:

> On 2013-02-26, Gustaf Mossakowski wrote:
>> Wie tausche ich am einfachsten den Wert im Feld reihenfolge, z. B.
>> so, dass bei nm_id 125 reihenfolge = 1 ist, bei nm_id 124
>> reihenfolge = 2?
>
> Das Feld reihenfolge brauchst Du doch gar nicht in der Tabelle. Das kann
> und sollte man besser berechnen:

Das funktioniert, vielen Dank, ist aber nicht das, was ich brauche. Ich
ordne Bilder zu Nachrichten zu und m�chte die Reihenfolge der
Darstellung �ndern. Die soll aber nicht immer nach der laufenden ID der
Tabelle sortiert sein, also nicht unbedingt die Reihenfolge der Eingabe
der Datens�tze. Daher das zus�tzliche Feld `reihenfolge`.

Viele Gr��e, Gustaf

[Dieter Nöth]

Gustaf Mossakowski wrote:

> CREATE TABLE IF NOT EXISTS `nachrichten_medien` (
> `nm_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
> `nachricht_id` int(10) unsigned NOT NULL DEFAULT '0',
> `medium_id` int(10) unsigned NOT NULL DEFAULT '0',
> `reihenfolge` smallint(5) unsigned NOT NULL DEFAULT '1',
> PRIMARY KEY (`nm_id`),
> UNIQUE KEY `nachricht_medium` (`nachricht_id`,`medium_id`),
> UNIQUE KEY `reihenfolge` (`reihenfolge`,`nachricht_id`)
> ) ENGINE=MyISAM;
>
> Die Daten sehen z. B. so aus:
>

> +-------+--------------+-----------+-------------+
> | nm_id | nachricht_id | medium_id | reihenfolge |
> +-------+--------------+-----------+-------------+
> | 125 | 169 | 1177 | 2 |
> | 124 | 169 | 1178 | 1 |
> | 126 | 169 | 1179 | 3 |
> +-------+--------------+-----------+-------------+
>

> Wie tausche ich am einfachsten den Wert im Feld reihenfolge, z. B. so,
> dass bei nm_id 125 reihenfolge = 1 ist, bei nm_id 124 reihenfolge = 2?
>
> So etwas geht nicht:
>
> mysql> UPDATE nachrichten_medien
> SET reihenfolge = CASE nm_id
> WHEN 125 THEN 1
> WHEN 124 THEN 2
> END
> WHERE nm_id IN (124, 125);
> ERROR 1062 (23000): Duplicate entry '2-169' for key 'reihenfolge'
>
> Gibt es da etwas oder geht das nicht direkt?

Da gibt es was, ein mySQL-Problem :-)
Natürlich müsste das so gehen, aber mySQL kriegt's nicht auf die Reihe,
sondern ändert stur ein physikalischen Record nach dem anderen und
überprüft jedesmal sofort den Constraint.

Zwei mögliche Lösungen:
#1
Schmeiss den UNIQUE "reihenfolge" einfach raus, ist der tatsächlich zu
irgendwas nütze? Ein Sortieren geht auch wenn's nicht eindeutig ist.
(Ich würde wahrscheinlich auch die Spalte nm_id rausschmeissen, aber das
ist ein anderes Thema).

#2
Mach drei Updates und nimm einen Dummy-Wert:
UPDATE nachrichten_medien
SET reihenfolge = 99999
WHERE nm_id = 124;
UPDATE nachrichten_medien
SET reihenfolge = 1
WHERE nm_id = 125;
UPDATE nachrichten_medien
SET reihenfolge = 2
WHERE nm_id = 124;

Eventuell gibt's auch eine andere ENGINE als MyISAM, die solche
Basisoperationen ohne Verrenkungen unterstützt?

Dieter

[Bernd Nawothnig]

On 2013-02-27, Gustaf Mossakowski wrote:
> Das funktioniert, vielen Dank, ist aber nicht das, was ich brauche. Ich

> ordne Bilder zu Nachrichten zu und möchte die Reihenfolge der
> Darstellung ändern.

Was verstehst Du denn unter "Darstellung"? Kann es sein, dass Du nur
die ORDER BY Klausel suchst?

> Die soll aber nicht immer nach der laufenden ID der Tabelle sortiert
> sein, also nicht unbedingt die Reihenfolge der Eingabe der

> Datensätze. Daher das zusätzliche Feld `reihenfolge`.

Wenn es nur um die Reihefolge des Ergebnissets geht, ist die eh ohne
ORDER BY Klausel nicht garantiert. Sie kann sich etwa bei der nächsten
MySQL-Version ohne Ankündigung ändern. Das verstieße nicht gegen den
Standard.




Bernd

[Bernd Nawothnig]

On 2013-02-27, Dieter Nöth wrote:
> Da gibt es was, ein mySQL-Problem :-)

Richtig. MySQL hält sich nicht an den Standard und kennt nicht die
DEFERRABLE INITIALLY DEFERRED Klausel, die selbst SQLite beherrscht
und die hier das Problem lösen würde.

> Natürlich müsste das so gehen, aber mySQL kriegt's nicht auf die Reihe,
> sondern ändert stur ein physikalischen Record nach dem anderen und
> überprüft jedesmal sofort den Constraint.

Exakt. Das macht die InnoDB Engine wohl hart kodiert zeilenweise (s.u.).

> Zwei mögliche Lösungen:
> #1
> Schmeiss den UNIQUE "reihenfolge" einfach raus, ist der tatsächlich zu
> irgendwas nütze? Ein Sortieren geht auch wenn's nicht eindeutig ist.
> (Ich würde wahrscheinlich auch die Spalte nm_id rausschmeissen, aber das
> ist ein anderes Thema).

Oder erst wegwerfen, dann ändern und anschließend wieder erzeugen.

> #2
> Mach drei Updates und nimm einen Dummy-Wert:
> UPDATE nachrichten_medien
> SET reihenfolge = 99999
> WHERE nm_id = 124;
> UPDATE nachrichten_medien
> SET reihenfolge = 1
> WHERE nm_id = 125;
> UPDATE nachrichten_medien
> SET reihenfolge = 2
> WHERE nm_id = 124;
>
> Eventuell gibt's auch eine andere ENGINE als MyISAM, die solche
> Basisoperationen ohne Verrenkungen unterstützt?

Laut Google geht es auch mit InnoDB nicht:

http://stackoverflow.com/questions/5014700/in-mysql-can-i-defer-referential-integrity-checks-until-commit





Bernd

[Gustaf Mossakowski]

Bernd Nawothnig schrieb:

> On 2013-02-27, Gustaf Mossakowski wrote:
>> Das funktioniert, vielen Dank, ist aber nicht das, was ich brauche.

>> Ich ordne Bilder zu Nachrichten zu und m�chte die Reihenfolge der
>> Darstellung �ndern.
>

> Was verstehst Du denn unter "Darstellung"? Kann es sein, dass Du nur
> die ORDER BY Klausel suchst?

Nee. ORDER BY geht ja nur, wenn man was zum Sortieren hat. Und wenn ich
'nen Bild hab und 'ne Nachricht, kann ich zwar nach dem Datum/Titel/was
auch immer der Nachricht oder nach Datum/Titel/was auch immer des Bildes
suchen, aber keine individuelle Reihenfolge festlegen. Daf�r ist die
Spalte `reihenfolge` da. Aber das Problem ist ja nun eh (nicht) gel�st.
Vielen Dank

Viele Gr��e, Gustaf

[Gustaf Mossakowski]

Dieter N�th schrieb:

> Zwei m�gliche L�sungen:
> #1
> Schmeiss den UNIQUE "reihenfolge" einfach raus, ist der tats�chlich zu
> irgendwas n�tze? Ein Sortieren geht auch wenn's nicht eindeutig ist.

Ja. In dem Fall geht das, da werde ich das auch machen. An einer anderen
Stelle darf zumindest reihenfolge = 1 nur 1x vorkommen, sonst wird die
Abfrage zu kompliziert. Aber das ist wahrscheinlich wirklich die
einfachste L�sung.

> (Ich w�rde wahrscheinlich auch die Spalte nm_id rausschmeissen, aber das
> ist ein anderes Thema).

Ja, die ist theoretisch nat�rlich unn�tig, aber kostet ja auch nicht
sooviel Speicherplatz. Mein Framework nutzt die zum direkten Bearbeiten
der Detaildatens�tze.

> #2
> Mach drei Updates und nimm einen Dummy-Wert:
> UPDATE nachrichten_medien
> SET reihenfolge = 99999
> WHERE nm_id = 124;
> UPDATE nachrichten_medien
> SET reihenfolge = 1
> WHERE nm_id = 125;
> UPDATE nachrichten_medien
> SET reihenfolge = 2
> WHERE nm_id = 124;

Vielleicht mit SET reihenfolge = SELECT MAX(reihenfolge) + 1 FROM
nachrichten_medien WHERE nachricht_id = 169 oder so. Da mu� man dann nur
auf Race conditions aufpassen ...hmmm ... Aber vielen Dank f�r die
Antwort!

Viele Gr��e, Gustaf

[Claus Reibenstein]

Gustaf Mossakowski schrieb:

> Da muￜ man dann nur
> auf Race conditions aufpassen

Transaktionen existieren. Setzen allerdings die Verwendung einer
geeigneten Speicher-Engine voraus, z.B. InnoDB.

Gruￜ
Claus