SQL UPDATE out of SELECT

[MichaelS]

I want to select all customers that have'nt written me in the last 3 years although I have writen them at least 5 times in the last 3 years.

So I built the folowing query:

SELECT KuIndex, Leben, Stubser FROM (SELECT Kontakte.KuIndex, MAX(IF(Kontaktart.Lebenszeichen = 1, Kontakte.Zeitpunkt, NULL)) AS Leben, SUM(IF(Kontaktart.Lebenszeichen IS NULL AND Kontakte.Zeitpunkt>DATE_ADD(CURDATE(), INTERVAL -3 YEAR), 1, 0)) AS Stubser FROM Kontakte INNER JOIN Kontaktart ON (Kontakte.Titel=Kontaktart.Kontaktart) GROUP BY Kontakte.KuIndex) Liste WHERE ((Stubser > 5) AND (Leben<DATE_ADD(CURDATE(), INTERVAL -3 YEAR)))

This query workes fine and shows all the customers suiting my affords.

As far - as good.

But now I want to SET the field Kunden.DeleteIntern of all this customers to 1. I've tried a few combinations like "UPDATE Kunden SET Kunden.DeleteIntern=1 WHERE (SELECT ...)" but everything causes an error.

Has anybody an idea how to add an UPDATE to my query?

regards Michael

[Tabellenkalk]

Hallo,

I'm not the one to answer your question, but I'm curious about you asking in english but doing your table design in german. why?

Gruß
Kalk

[MichaelS]

Hab erst ein paar Beiträge hier gelesen und die waren alle auf Englisch. Da dachte ich: Das ist hier so üblich 😀 liebe Grüße Michael

[bobby]

Moin,

your where clause must be:

UPDATE Kunden SET Kunden.DeleteIntern=1 WHERE Kunden.KuIndex IN (SELECT KuIndex FROM  ...)

Gruß Bobby

[MichaelS]

Hallo Bobby,

danke für die flotte Antwort habe ich probiert und Folgendes formuliert:

UPDATE Kunden 
SET Kunden.DeleteIntern=1 
Where Kunden.KuIndex IN 
      (SELECT KuIndex, Leben, Stubser 
       FROM (SELECT Kontakte.KuIndex, 
             MAX(IF(Kontaktart.Lebenszeichen = 1, Kontakte.Zeitpunkt, NULL)) AS Leben,
             SUM(IF(Kontaktart.Lebenszeichen IS NULL 
                  AND Kontakte.Zeitpunkt>DATE_ADD(CURDATE(), INTERVAL -3 YEAR), 1, 0)) AS Stubser
             FROM Kontakte INNER JOIN Kontaktart ON (Kontakte.Titel=Kontaktart.Kontaktart) 
             GROUP BY Kontakte.KuIndex) Liste 
       WHERE ((Stubser > 5) AND (Leben<DATE_ADD(CURDATE(), INTERVAL -3 YEAR))))

Das geht aber leider nicht :-(

MySQL meldet: #1241 - Operand sollte 1 Spalte(n) enthalten

*Edit Rolf B: SQL Formatiert und ~~~sql / ~~~ hinzugefügt

[bobby]

Moin,

Das select in der where clause darf natürlich nur eine Liste der ID zurückgeben... lösche ais dem Select alle Felder außer der Id. Siehe mein Post. Da hatte ich das bereits korrigiert.

Gruß Bobby

[Matthias Apsel]

Hallo MichaelS,

UPDATE Kunden SET Kunden.DeleteIntern=1 Where Kunden.KuIndex IN (SELECT KuIndex, Leben, Stubser FROM (SELECT Kontakte.KuIndex, MAX(IF(Kontaktart.Lebenszeichen = 1, Kontakte.Zeitpunkt, NULL)) AS Leben, SUM(IF(Kontaktart.Lebenszeichen IS NULL AND Kontakte.Zeitpunkt>DATE_ADD(CURDATE(), INTERVAL -3 YEAR), 1, 0)) AS Stubser FROM Kontakte INNER JOIN Kontaktart ON (Kontakte.Titel=Kontaktart.Kontaktart) GROUP BY Kontakte.KuIndex) Liste WHERE ((Stubser > 5) AND (Leben<DATE_ADD(CURDATE(), INTERVAL -3 YEAR))))

Auch SQL lässt sich so formatieren, dass man es lesen kann.

Bis demnächst
Matthias

[Rolf B]

Hallo Matthias,

ich habe das oben mal gemacht.

Michael, damit Programmcode (PHP, HTML, CSS, JavaScript, SQL) als solcher erkannt wird, musst Du ihn hier im Forum in ~~~ einrahmen.

Beispiel (mit Leerstellen vor dem ~~~, damit es nicht interpretiert wird):

 ~~~sql  
 SELECT foo FROM bar WHERE baz=7  

Hinter dem ersten ~~~ kann man noch die Sprache angeben. 

Zu deiner Query: Muss es ein geschachtelter Select sein? Normalerweise sollte man ein GROUP BY Ergebnis durch eine nachgelagertes HAVING-Klausel filtern können. Wenn Du MYSQL benutzt, kannst Du im HAVING Spalten und Terme benutzen, die in der SELECT-Liste gar nicht vorkommen (das geht in anderen SQL Dialekten nicht). Man muss auch nicht so viele Klammern setzen wie Du es getan hast...

Vorschlag:

~~~sql
UPDATE Kunden 
SET Kunden.DeleteIntern=1 
Where Kunden.KuIndex IN 
      (SELECT Kontakte.KuIndex
       FROM Kontakte INNER JOIN Kontaktart ON (Kontakte.Titel=Kontaktart.Kontaktart) 
       GROUP BY Kontakte.KuIndex
       HAVING SUM(IF(Kontaktart.Lebenszeichen IS NULL 
                  AND Kontakte.Zeitpunkt > DATE_ADD(CURDATE(), INTERVAL -3 YEAR), 1, 0)) > 5
          AND MAX(IF(Kontaktart.Lebenszeichen = 1, Kontakte.Zeitpunkt, NULL)) 
                                  < DATE_ADD(CURDATE(), INTERVAL -3 YEAR)

Rolf

[MichaelS]

Hallo Rolf,

vielen Dank für die Lösung. Ich habe noch die fehlende ) am Ende ergänzt und die Abfrage gestartet. Sie liefert nun tatsächlich das gewünschte Ergebnis. 😀

Allerdings legt sie dabeih für ca. 1 Stunde meinen SQL-Server so lahm, dass kein Programm mehr darauf zugreifen kann. Und das bei …

4.119 Kunden 50.329 Kontakten und 117 Kontaktarten.

So kann ich die Abfrage daher leider auch nicht verwenden. 😟

Siehst du denn eine Möglichkeit, das Ganze noch irgendwie zu beschleunigen? (ev. mit Hilfstabellen?)

liebe Grüße Michael

[Rolf B]

Hallo MichaelS,

legt nur meine Variante den Server lahm? Oder deine auch? Ich habe mir eine Weile Gedanken gemacht, ob man die Query besser gestalten könnte. Deine IFs gefallen mir nicht wirklich. Aber die Alternativen, die ich gefunden hatte, machen 3 Joins statt einen, und das ist vermutlich noch weniger effizient.

Bei Performanceproblemen lautet die erste Anlaufstelle immer EXPLAIN, und dann muss man schauen, welchen Ausführungsplan er für die Query wählt.

Die Größe der Kundentabelle ist egal, die ist an der Query nicht beteiligt. 50K Kontakte bei 117 Kontaktarten sind natürlich schon eine Menge, das sind potenziell 5 Millionen Kombinationen.

Ohne Kenntnis des Explain hätte ich folgende notorische Kandidaten für Langsamkeit:

Wenn die Spalte Kontaktart.Kontaktart keinen Index hat, muss der Server pro Eintrag in Kontakte einen Tablescan auf Kontaktart machen. Mit Index wäre es ein Index-Seek. Um einen Vorschlag für einen optimalen Index machen zu können, müsstest Du mal die Table-Definition herzeigen (Spalten, Primary Key, existierende Indexe).

Ist Kontakte.KuIndex erster Teil des Primary Key von Kontakte? Hat der Primary Key einen Clustering-Index? Wenn nicht, kostet auch der group by viel Zeit weil er dann sortieren muss.

Rolf

[MichaelS]

Danke Rolf nochmals für dein Engagement.

Ich melde mich jetzt mal kurz, damit du nicht glaubst, ich bin "eingeschlafen" 😀 Ich baue gerade meine Kundendatenbank und meine Homepage um, damit in der Tabelle "Kontakte" in Zukunft nicht mehr ein freier Text als Titel eingegeben werden kann, sondern nur noch der eindeutige Primärschlüssel aus der Tabelle Kontaktart.

Diese Änderung sollte die Abfrage dann massiv beschleunigen. Ich melde mich wieder, sobald es was Neues gibt 😀

liebe Grüße Michael

[Matthias Apsel]

Hallo Rolf B,

Michael, damit Programmcode (PHP, HTML, CSS, JavaScript, SQL) als solcher erkannt wird, musst Du ihn hier im Forum in ~~~ einrahmen.

Das hätte ich auch gemacht. Ohne großen Hinweis. Allerdings meinte ich eher sinnvolle Strukturierungen durch Zeilenumbrüche und Einrückungen.

Bis demnächst
Matthias