SQL Update über alle Spalten einer Tabelle

[Cortys]

Hallo allerseits!
Ich habe eine MySQL-Tabelle mit rech vielen Spalten (65).

Bevor sich hier noch jemand über unsauberes Layout aufregt: Ich habe an alle Normalformen gedacht und die Struktur ist sauber. ;-)

In der Tabelle haben manche Datensätze in mehreren Spalten den Integer-Wert 0, diesen möchte ich durch NULL ersetzen.

Da es zu viel Arbeit wäre jede Spalte einzeln in einen Update zu packen, wollte ich fragen, wie ich so etwas mit einer Schleife lösen kann (bisher gar keine Erfahrung damit).

Danke für jede Hilfe!

[Encoder]

Da es zu viel Arbeit wäre jede Spalte einzeln in einen Update zu packen

zur Not erstellst du eine Vorlage für ein UPDATE einer einzigen Spalte, kopierst die 65 mal und kopierst da dann jeweils die Spaltennamen rein. So ewig dauert das nicht.
Etwas komfortabler wirds wenn du dir das SQL aus einer Query zusammensetzen lässt, die die einzelnen Spaltennamen in ein Statement einbaut.
UPDATE ... SET <hier Spalte einsetzen> = NULL WHERE <Spalte> = 0

Wie man da ran kommt weiß ich nicht, findest du aber sicher in Google.

[Cortys]

Etwas komfortabler wirds wenn du dir das SQL aus einer Query zusammensetzen lässt, die die einzelnen Spaltennamen in ein Statement einbaut.
UPDATE ... SET <hier Spalte einsetzen> = NULL WHERE <Spalte> = 0

Wie man da ran kommt weiß ich nicht, findest du aber sicher in Google.

So hätte ich das notfalls auch gemacht, aber eine reine SQL-Lösung wäre mir lieber, damit ich das direkt aus phpMyAdmin heraus machen kann.
Hab ich eben vergessen deutlich zu machen.

[Encoder]

eine reine SQL-Lösung wäre mir lieber

Das ist doch eine. SQL ausführen, dann kriegst du als Ergebnis die einzelnen Zeilen mit den neuen Statements drin. Die kopierst du ins Fenster zurück und führst sie wieder aus, fertig.

[misterunknown]

Moin,

Da es zu viel Arbeit wäre jede Spalte einzeln in einen Update zu packen, wollte ich fragen, wie ich so etwas mit einer Schleife lösen kann (bisher gar keine Erfahrung damit).

  
function getColumns($table){  
      $table = mysql_real_escape_string($table);  
      $mysqlres = mysql_query("SHOW COLUMNS FROM ".$table);  
      while($row = mysql_fetch_assoc($mysqlres)){  
        $res[] = $row;  
      }  
      return $res;  
    }

Quelle

Die Schleife zum Abarbeiten der Spalten kriegst du selbst hin, oder!?

Grüße Marco

[misterunknown]

Moin,
zur Info noch: mit der von mir geposteten Funktion wird ein mehrdimensionales Array erstellt, welches nicht nur die Spaltennamen enthält, sondern auch Typ etc. Willst du gleich nur die Spaltennamen bekommen, schreibst du die while-Schleife folgendermaßen um:

       while($row = mysql_fetch_assoc($mysqlres)){  
         $res[] = $row["Field"];  
       }  

Grüße Marco

[Matti Mäkitalo]

Hi,

Ich habe eine MySQL-Tabelle mit rech vielen Spalten (65).

Bevor sich hier noch jemand über unsauberes Layout aufregt: Ich habe an alle Normalformen gedacht und die Struktur ist sauber. ;-)

In der Tabelle haben manche Datensätze in mehreren Spalten den Integer-Wert 0, diesen möchte ich durch NULL ersetzen.

Da es zu viel Arbeit wäre jede Spalte einzeln in einen Update zu packen, wollte ich fragen, wie ich so etwas mit einer Schleife lösen kann (bisher gar keine Erfahrung damit).

mir fällt auf Anhieb kein anderer Weg ein als in einer Programmiersprache deiner Wahl die Spaltendefinition abzufragen (z.B. mit DESCRIBE) und dann manuell ein SQL-Statement zusammenzusetzen.

Eine Möglichkeit wäre es, dies in SQL zu machen: da baust du dein SQL-Statement in einem String zusammen (etwa in einer Stored Procedure) und nutzt dann Prepared-Statements (quasi als eval()-Ersatz).

Bis die Tage,
Matti

[dedlfix]

Hi!

In der Tabelle haben manche Datensätze in mehreren Spalten den Integer-Wert 0, diesen möchte ich durch NULL ersetzen.
Da es zu viel Arbeit wäre jede Spalte einzeln in einen Update zu packen, wollte ich fragen, wie ich so etwas mit einer Schleife lösen kann (bisher gar keine Erfahrung damit).

Es geht in einem einzigen Update-Statement.

UPDATE SET spalte1 = IF(spalte1 = 0, NULL, spalte1), spalte2 = IF(...

Das Problem ist, dass man trotzdem jede Spalte einzeln ansprechen muss. Es gibt kein UPDATE SET * = irgendwas. Es bleibt, die Spaltennamen entweder zu ermitteln oder als Liste vorliegen zu haben, und daraus das eine oder viele Einzel-Updates zu erstellen. Innerhalb SQLs wie schon erwähnt mit einer Stored Procedure, Prepared Statement (à la eval()) und die Spaltennamen bekommt man über die Pseudo-Datenbank INFORMATION_SCHEMA.

Lo!

[Sven Rautenberg]

Moin!

Bevor sich hier noch jemand über unsauberes Layout aufregt: Ich habe an alle Normalformen gedacht und die Struktur ist sauber. ;-)

In der Tabelle haben manche Datensätze in mehreren Spalten den Integer-Wert 0, diesen möchte ich durch NULL ersetzen.

0 und NULL ist nicht dasselbe. Ich hoffe, das ist dir bewußt. :)

Ansonsten gibts bei Funktionen wir count() oder avg() eine Überraschung. Und auch schon bei "WHERE spalte = NULL".

- Sven Rautenberg

[Ilja]

moin,

In der Tabelle haben manche Datensätze in mehreren Spalten den Integer-Wert 0, diesen möchte ich durch NULL ersetzen.

0 und NULL ist nicht dasselbe. Ich hoffe, das ist dir bewußt. :)

um das noch mal zu unterstreichen, bei uns in der firma dürfen bestimmte datentypen wie integer gründsätzlich nicht NULL werte annehmen und sind per default immer 0. das hat sich in der praxis bewährt, weil oftmals mit dem werten gerechnet wird und es dann zu unschönen ergebnissen kommen kann. das ist kein dogma, aber eine richtlinie, die bisher mehr vorteile als nachteile hat....

Ilja

[dedlfix]

Hi!

um das noch mal zu unterstreichen, bei uns in der firma dürfen bestimmte datentypen wie integer gründsätzlich nicht NULL werte annehmen und sind per default immer 0. das hat sich in der praxis bewährt, weil oftmals mit dem werten gerechnet wird und es dann zu unschönen ergebnissen kommen kann. das ist kein dogma, aber eine richtlinie, die bisher mehr vorteile als nachteile hat....

Da gab es doch mal so ein schönes Thermometer-Beispiel. Wenn es ausgefallen ist, muss der Wert NULL sein, denn 0 (°C) zu speichern, nur weil im Sommer der Messfühler einen Hitzekollaps hatte, verfälscht die Messreihe.

Auch bei der Lagerhaltung ist deine Richtline, wenn man sie blind befolgt, nicht unbedingt von Vorteil. Der RFID-Leser am Regal X ist defekt, als Messergebnis wird nach der Richtlinie die Anzahl 0 in den Bestand eingetragen. Wenn nun der Einkauf tätig wird, bekommt man einen Überbestand im Lager. In der Theorie hat man zwar den defekten Leser sofort erkannt und ausgetauscht, aber in der Praxis ist der zuständige Kollege grad ein paar Tage nicht da ... Richtlinien sind zwar schön und einfach, sie aber nie zu hinterfragen, bringt auch Nachteile mit sich. Die "Wahrheit" liegt mal wieder irgendwo in der Mitte.

Lo!

[Ilja]

moin,

Da gab es doch mal so ein schönes Thermometer-Beispiel. Wenn es ausgefallen ist, muss der Wert NULL sein, denn 0 (°C) zu speichern, nur weil im Sommer der Messfühler einen Hitzekollaps hatte, verfälscht die Messreihe.

mit der temperatur würde ich mich in der tat auch ein wenig schwer tun, weil dort eine 0 nicht wirklich den gleichen neutralen charakter wie zum beispiel bei geld hat. aber selbst dann, frage ich mich, warum dort kein NOT NULL constraint vorliegt. schließlich will ich doch keine messungen im system haben, die keine messwerte haben. worin besteht da der sinn ? aber klar, temperatur wäre eventuell eine ausnahme. man muss es eben genau anschauen.

Auch bei der Lagerhaltung ist deine Richtline, wenn man sie blind befolgt, nicht unbedingt von Vorteil.

richtlinien blind zu befolgen macht ja auch keinen sinn. aber man muss gründe haben, warum man davon abweichen will.

Der RFID-Leser am Regal X ist defekt, als Messergebnis wird nach der Richtlinie die Anzahl 0 in den Bestand eingetragen.

auch dem kann ich mich nicht anschließen, wobei ich die funktionsweise des RFID-Leser nicht kenne. aber erstens wenn er falsche werte überträgt, dann spielen sowohl der NOT NULL als auch der Default wert keine rolle. er schreibt eben einen falschen wert rein. und wenn ich nicht will, das er den default wert nimmt, dann kann ich ja wohl die applikation so zu recht schnippeln, dass er erst gar keinen datensatz wegschreibt, wenn das attribut NULL ist....

Ilja

[dedlfix]

Hi!

schließlich will ich doch keine messungen im system haben, die keine messwerte haben. worin besteht da der sinn ? aber klar, temperatur wäre eventuell eine ausnahme. man muss es eben genau anschauen.

Ich hatte im Sinn, dass zumindest die anderen Messfühler weiterarbeiten, nur der eine in der Sonne aussteigt. Aber das läuft auf ein ungünstiges Design hinaus, wenn jeder seine eigene Spalte bekommt statt dass alle Messwerte in einer Spalte stehen mit einem 1:n-Verweis auf den Messzeitpunkt. Das Problem löst sich also auf, wenn man das Design ordentlich gestalten kann.

Auch bei der Lagerhaltung ist deine Richtline, wenn man sie blind befolgt, nicht unbedingt von Vorteil.
richtlinien blind zu befolgen macht ja auch keinen sinn. aber man muss gründe haben, warum man davon abweichen will.

Ich wollte sie dir nicht ausreden und habe sie auch nicht als Dogma aufgefasst. Lediglich ein paar Beispiele habe ich gesucht, bei denen man davon abweichen muss.

Lo!

[Ilja]

moin,

Ich wollte sie dir nicht ausreden und habe sie auch nicht als Dogma aufgefasst. Lediglich ein paar Beispiele habe ich gesucht, bei denen man davon abweichen muss.

ich glaube auch, dass es diese beispiele gibt, aber ehrlich gesagt habe ich noch keine gesehen, man kennt halt nicht alles. aber ich wage mich trotzdem mal auf das unbekannte feld hinaus und behaupte zu 99,999 % kannst du alle Zahlenwerten den NOT NULL Constraint verpassen, bei unserer Firma liegen wir im Moment bei 100% und das nenne ich mal eine recht harte Richtlinie...

Ilja

[Encoder]

um das noch mal zu unterstreichen, bei uns in der firma dürfen bestimmte datentypen wie integer gründsätzlich nicht NULL werte annehmen und sind per default immer 0.

Wie heißt die Firma? Nicht dass ich da mal was bestelle ;-)

weil oftmals mit dem werten gerechnet wird

NULL ist dazu da um es zu setzen wenn es Sinn macht. Ich vermeide NULL auch wo es geht, wenn eine Null passt kommt eine rein. Aber wenns halt nicht geht, weil eine Null und die Info "hier steht gar nichts drin" einen Unterschied macht, dann kommt NULL rein.

Es grundsätzlich zu verbieten ist Quatsch, das sagt mir dass der Verfasser dieser Richtlinie keine Ahnung hat. Find ich schade.

[Ilja]

moin,

um das noch mal zu unterstreichen, bei uns in der firma dürfen bestimmte datentypen wie integer gründsätzlich nicht NULL werte annehmen und sind per default immer 0.
Wie heißt die Firma? Nicht dass ich da mal was bestelle ;-)

sage mir doch mal deinen namen, damit wir dich nicht aus versehen einstellen ;-)

NULL ist dazu da um es zu setzen wenn es Sinn macht. Ich vermeide NULL auch wo es geht, wenn eine Null passt kommt eine rein. Aber wenns halt nicht geht, weil eine Null und die Info "hier steht gar nichts drin" einen Unterschied macht, dann kommt NULL rein.

dann erkläre mir doch mal, wie du NULL in eine spalte bekommst, die einen NOT NULL Constraint hat oder gibt es sowas in deinen daten-design nicht ?

Es grundsätzlich zu verbieten ist Quatsch, das sagt mir dass der Verfasser dieser Richtlinie keine Ahnung hat. Find ich schade.

keine ahnung was du unter einer richtlinie verstehst, aber eine richtline ist für mich ein weg, den man einhält, solange es keine guten grund/gründe gibt, diesen weg zu verlassen. also von "immer" einhalten kann ja schon mal nicht die rede sein. aber bei den attributen, die wir haben (und da ist ekine temperatur messung dabei), hat sich diese richtlinie als vorteilhaft erwiesen, vor allem weil wir viel mit geldbeträgen arbeiten und zum teil auch mit mengen.

Ilja

[Encoder]

dann erkläre mir doch mal, wie du NULL in eine spalte bekommst, die einen NOT NULL Constraint hat oder gibt es sowas in deinen daten-design nicht ?

Wenn sie diesen Constraint hat geht das natürlich nicht. Nur, wenns sinnvoll ist lass ich diesen Constraint eben weg.

Klar, wenn ihr das bei euren Werten so braucht dann macht ihr es natürlich so. Deine Beschreibung im ersten Beitrag klang nur sehr strikt, ein Datentyp wie int hat grundsätzlich nie null... Hört sich an als wär das eine Vorschrift an die man sich zu halten hat, egal was die Situation gerade erfordert. Und das wär ja schade.

[Ilja]

moin,

Klar, wenn ihr das bei euren Werten so braucht dann macht ihr es natürlich so. Deine Beschreibung im ersten Beitrag klang nur sehr strikt, ein Datentyp wie int hat grundsätzlich nie null... Hört sich an als wär das eine Vorschrift an die man sich zu halten hat, egal was die Situation gerade erfordert. Und das wär ja schade.

Praktisch ist es bei uns im moment so. das heißt nicht, dass wir nicht von der regel abweichen würden, wenn wir einen sinn darin sehen würden. aber im moment sind alle zahlenwerte NOT NULL und es hat sich bewährt.

Ilja