MySQL: INSERT INTO oder UPDATE verknüpft in einem Query

[Gast]

Hey,

wollte mal wissen ob es möglich ist in einem Query entweder INSERT INTO auszuführen (falls Eintrag noch nicht vorhanden) oder UPDATE (falls eintrag vorhanden) auszuführen.

Das ganze jedoch OHNE on duplicate key entry, da es sich bei den Spalten nicht um Prim. Keys handelt.

Tabellen Struktur sieht folgendermaßen aus:

`id` - `attribute` - `val`

Für alle IDs können verschiedene Attribute u. Werte gespeichert werden:

ID | attribute | val
1    Attr_1        2
1    Attr_2        5
2    Attr_5        6
2    Attr_8        1

Nun möchte ich z.B. für ID 1 das Attribut 1 um 5 als auch für ID 2 (default value ist 0 von daher wäre es also 0+5) mit demselben Query erhöhen können, da ich im vorhinein nicht mit einem extra Query überprüfen möchte ob die Zeile bereits existiert.

Hab ebenfalls REPLACE gegoogelt, allerdings kann ich da nicht mit WHERE arbeiten.

Ist es generell möglich das zu tun oder muss ich z.B. zunächst ein UPDATE query ausführen und falls keine Zeilen betroffen sind ein INSERT INTO hinterher schmeißen?

Vielen Dank im Vorraus.

[thomas_w]

Schau Dir mal "REPLACE" an.

REPLACE [LOW_PRIORITY | DELAYED]
[INTO] tbl_name [(col_name,...)]
VALUES ({expr | DEFAULT},...),(...),...

REPLACE funktioniert auf exakt gleiche Weise wie INSERT. Der Unterschied besteht darin, dass, wenn ein alter Datensatz
denselben Wert wie ein neuer Datensatz für einen Primärschlüssel oder einen eindeutigen Index hat, der alte Datensatz gelöscht
wird, bevor der neue eingefügt wird. Siehe auch Abschnitt 13.2.4,

Grüße
Thomas

[Gast]

Sorry meinte natürlich "REPLACE" nicht "REPLACEMENT".

Problem: Meines Wissens nach kann ich REPLACE nicht mit "WHERE" verknüpfen, ist das korrekt?

[nikosch]

INSERT ja auch nicht. Wozu auch? Im Normalfall kann man im Vorfeld doch die Daten zusammenstellen, die man einfügen will.

[Gast]

Hast du meinen ersten Post gelesen?

Ich kann leider nicht mit REPLACE arbeiten. Würde ich z.B. für ID 1 das Attribut 2 um 5 erhöhen wollen würde das ganze so aussehen:

REPLACE INTO table (`id`,`attr,`val`) VALUES (1,5,`val`+5)

mit dem Ergebnis dass eine neue Zeile hinzukommt:

ID | attribute | val
1    Attr_1        2
1    Attr_2        5
2    Attr_5        6
2    Attr_8        1
1    Attr_2        5

Das erwünschte Ergebnis wäre jedoch:

ID | attribute | val
1    Attr_1        2
1    Attr_2        10
2    Attr_5        6
2    Attr_8        1

Und mit dem Query gleicher Art für ID 2 (die Attr_2 noch nicht als Eintrag in der DB hat):

ID | attribute | val
1    Attr_1        2
1    Attr_2        10
2    Attr_5        6
2    Attr_8        1
2    Attr_2        5

... nur für den Fall dass es überhaupt möglich ist und deswegen frage ich.

[thomas_w]

Bleibt vielleicht noch das Thema BEFORE UPDATE TRIGGER (BUT) und/oder Stored Procedures (SP)

Der Ablauf wäre dann wie folgt:

1. Einen UPDATE mit den neuen Daten ausführen. Dies aktiviert den BUT
2. Findet der BUT mit Hilfe eines SELECT den PK macht er nichts und der UPDATE wird ausgeführt.
3. Findet der BUT den PK nicht, erzeugt er einen INSERT.

Wäre natürlich auch eine eigenes SP dafür sinnvoll.

Mal als Idee...

Grüße
Thomas

[nikosch]

Du kannst UPDATE und INSERT verwenden. Aber was hat das mit WHERE zu tun?

[Gast]

Bleibt vielleicht noch das Thema BEFORE UPDATE TRIGGER (BUT) und/oder Stored Procedures (SP)

Der Ablauf wäre dann wie folgt:

1. Einen UPDATE mit den neuen Daten ausführen. Dies aktiviert den BUT
2. Findet der BUT mit Hilfe eines SELECT den PK macht er nichts und der UPDATE wird ausgeführt.
3. Findet der BUT den PK nicht, erzeugt er einen INSERT.

Wäre natürlich auch eine eigenes SP dafür sinnvoll.

Mal als Idee...

Grüße
Thomas

Hey,

würde das ganze auf eine IF THEN Anweisung rauslaufen?
Könntest du mir dann die richtige Syntax nennen? Ich google das Problem jetzt schon seit ner Stunde und alle queries die ich darüber finde geben mir Syntax Errors zurück.

Meine IF THEN Anweisung wäre gewesen:

IF EXISTS(SELECT  * FROM table WHERE Attribut = attr_2 AND id=2)

    BEGIN

       UPDATE table SET val = val+5 WHERE Attribut = attr_2 

    END

    ELSE

    BEGIN

       INSERT INTO table (id, attribut, val) VALUES ('2','attr_2',5)

    END

Funktioniert leider nicht:

#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'IF EXISTS(SELECT * FROM p_inv WHERE item = 5) BEGIN UPDATE p_i' at line 1

(( Auch ohne BEGIN und END - ich bekomme keine IF THEN Anweisung hin, wenn THEN ein Query beinhält. ))

Du kannst UPDATE und INSERT verwenden. Aber was hat das mit WHERE zu tun?

Nochmal: Ich würde gerne in einem MySQL Query das genannte Problem lösen. Soll heißen, wenn z.B. die Zeile Attr_2 für ID 2 schon existiert soll er ein UPDATE des values für ID 2 Attr_2 ausführen.

Wenn die Zeile noch nicht existiert soll er sie neu hinzufügen.

[thomas_w]

würde das ganze auf eine IF THEN Anweisung rauslaufen?

Sinngemäß ja, aber MySQL kann diese Syntax nicht. Bleibt also der
sogenannte Trigger.

komplettes Beispiel aus dem Handbuch (suche mal nach CREATE TRIGGER oder CREATE PROCEDURES)

In MySQL 5.1, you can write triggers containing direct references to tables by name, such as the trigger named testref shown in
this example:

CREATE TABLE test1(a1 INT);
CREATE TABLE test2(a2 INT);
CREATE TABLE test3(a3 INT NOT NULL AUTO_INCREMENT PRIMARY KEY);
CREATE TABLE test4(
a4 INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
b4 INT DEFAULT 0
);

delimiter |

CREATE TRIGGER testref BEFORE INSERT ON test1
FOR EACH ROW BEGIN
INSERT INTO test2 SET a2 = NEW.a1;
DELETE FROM test3 WHERE a3 = NEW.a1;
UPDATE test4 SET b4 = b4 + 1 WHERE a4 = NEW.a1;
END;

|
delimiter ;

INSERT INTO test3 (a3) VALUES
(NULL), (NULL), (NULL), (NULL), (NULL),
(NULL), (NULL), (NULL), (NULL), (NULL);
INSERT INTO test4 (a4) VALUES
(0), (0), (0), (0), (0), (0), (0), (0), (0), (0);

Suppose that you insert the following values into table test1 as shown here:

mysql> INSERT INTO test1 VALUES
-> (1), (3), (1), (7), (1), (8), (4), (4);
Query OK, 8 rows affected (0.01 sec)
Records: 8 Duplicates: 0 Warnings: 0

Trigger lassen sich in etwa mit Datenbank-Ereignissen gleichsetzen. Nach dem Motto: "Wenn INSERT oder UPDATE oder DELETE auf dieser Tabelle, dann mach dies.."

Trigger und Stored Procedures sind eine eigene SQL-Sprache in der Datenbank und damit "eine eigene Welt".

Grüße
Thomas

[Gast]

Alles klar dann probier ich mal das Beispiel aus und vergleich die Laufzeiten.

Weißt du wie's da ist mit dem Ressourcenverbrauch? Ist der ungemein höher oder braucht man sich darüber eher keine Gedanken machen?

[nikosch]

Ich würde auf jeden Fall, mal drüber nachdenken (bzw. erfahren wollen) warum Du keine Keys verwenden kannst. Bspw. über (ID , attribute) sollte das doch möglich sein. Ansonsten erkenne ich nämlich keinen Sinn in diesem Beispiel, weil dann UPDATE oder INSERT ja ziemlich willkürlich wäre.

[thomas_w]

da so ein Trigger oder Stored Procedure in der Datenbank abläuft, fällt die komplette Übertragung (von PHP zur Datenbank) und das SQL-Parsing weg. Damit sollte eine SP schneller sein als reines SQL. Aber (aus-)probieren geht vor studieren...

Viel Erfolg!

Grüße
Thomas

[Gast]

Ich würde auf jeden Fall, mal drüber nachdenken (bzw. erfahren wollen) warum Du keine Keys verwenden kannst. Bspw. über (ID , attribute) sollte das doch möglich sein. Ansonsten erkenne ich nämlich keinen Sinn in diesem Beispiel, weil dann UPDATE oder INSERT ja ziemlich willkürlich wäre.

Naja, sowohl ID als auch Attribute können mehrfach auftreten. Schließt das die Verwendung von Keys nicht aus?

...

Sorry, muss nochmal nerven:

Hab jetzt ne Procedure nach diesem Beispiel (MySQL :: MySQL 5.1 Reference Manual :: 12.7.4.2 DECLARE for Handlers) aufgebaut:

CREATE PROCEDURE p ()
BEGIN
  DECLARE i INT DEFAULT 3;
  retry:
    REPEAT
      BEGIN
        DECLARE CONTINUE HANDLER FOR SQLWARNING
          BEGIN
            ITERATE retry;  # illegal
          END;
      END;
      IF i < 0 THEN
        LEAVE retry;        # legal
      END IF;
      SET i = i - 1;
    UNTIL FALSE END REPEAT;
END;

Sieht folgendermaßen aus:

Delimiter $$ CREATE PROCEDURE example_proc( )  BEGIN Declare var INT;

SELECT `id`
INTO var
FROM table
WHERE val ='5'
AND attribute='2'
AND id = '2';

IF var THEN UPDATE table SET val = val + '8' WHERE val ='5' AND attribute='2' AND id = '2';

ELSE INSERT INTO table( id, attribute, val ) value(
'2',
'2',
'8'
);
end if;
end;
$$

Problem:

#1327 - Undeclared variable: test

Ich verstehs grad einfach nicht und googlen hat nichts geholfen.

[nikosch]

Schließt das die Verwendung von Keys nicht aus?

Nein. Auch Schlüssel über mehrere Felder sind möglich, auch als PK.

[Gast]

Hm, könntest Du mir sagen, wie das Query dann aussehen muss? Ich hab jetzt sowohl `id` als auch `attribute` als PRIMARY KEY gesetzt.

Hier nochmal ne Bsp.Tabelle:

ID | attribute | val
1         1          2
1         2          3
2         2          5
2         3          4

Query:

INSERT INTO table (id, attribute, val) VALUES(1, 1, 5) ON DUPLICATE KEY UPDATE val = val + 5

(Das alle rows vom UPDATE betroffen wären ist erstmal egal.)

Allerdings wird nun nicht geupdated sondern eine neue Zeile mit den Werten vom INSERT INTO eingefügt.