Update mit Daten aus einer anderen Tabelle

[datenbeisser]

Hallo,

würde folgender Code funktionieren?

(die IDs sind in beiden Tabellen identisch und vorhanden):

SQLite > UPDATE Tabelle SET Name =
(SELECT Name_Neu FROM Tabelle_Neu WHERE Tabelle_Neu.ID == Tabelle.ID)

Denn wenn ich es richtig verstehe, wird in diesem Fall durch
"WHERE Tabelle_Neu.ID == Tabelle.ID"
sichergestellt, dass ein Update von "Name" nur stattfindet, wenn beide "IDs" identisch sind.

Falls der Code so funktioniert, ist mir nicht klar, wozu der Befehl EXISTS benötigt wird?

Grüße,
Datenbeisser

 

[akretschmer]

test=*# select * from name;
 id | name
----+------
  1 |
  2 |
  3 |
(3 rows)

Time: 77,286 ms
test=*# select * from name_neu;
 id |  name
----+--------
  1 | name 1
  3 | name 3
  4 | name 4
(3 rows)
test=*# update name set name=name_neu.name from name_neu where name.id=name_neu.id;
UPDATE 2
Time: 36,991 ms
test=*# select * from name;
 id |  name
----+--------
  2 |
  1 | name 1
  3 | name 3
(3 rows)

 

[datenbeisser]

Hallo akretschmer,

danke für das ausführliche Beispiel!

Dein SQL-Code ist wirklich sehr einfach und verständlich. Leider funktioniert er nicht mit SQLite, denn ich erhalte die Fehlermeldung, dass "FROM" an dieser Stelle nicht erlaubt ist. Schon wiederholt musste ich feststellen, dass SQLite oft von gängiger SQL Sprache abweicht. :-(

Ohne "EXISTS" funktioniert es anscheinend nicht mit SQLite. Nach langem Probieren habe ist es wie folgt hinbekommen:

UPDATE Table
SET
Name = (SELECT Table_Neu.Name_Neu FROM Table_Neu WHERE ID = Table.ID)
WHERE EXISTS (SELECT * FROM Table_Neu WHERE ID = Table.ID)

Total umständlich und weit weniger elegant und verständlich als Dein SQL Script. Aber damit müssen SQlite Programmierer anscheinend leben.

Nochmals vielen Dank,
Datenbeisser

 

[akretschmer]

Aber damit müssen SQlite Programmierer anscheinend leben.

Falls es Dich tröstet: MySQL-User fristen ebenfalls ein entbehrungsvolles Dasein.

 

[Distrilec]

UPDATE Table
SET
Name = (SELECT Table_Neu.Name_Neu FROM Table_Neu WHERE ID = Table.ID)
WHERE EXISTS (SELECT * FROM Table_Neu WHERE ID = Table.ID)

Ohne dieses Exists würde JEDER Datensatz geändert. Ungeachtet dessen ob Tabelle_Neu eine passende ID enthält.
D. h. sollte es in Tabelle_Neu eine ID nicht geben, wird der Name auf NULL (also nichts) geändert.

 

[akretschmer]

Man sollte vielleicht noch erwähnen, daß das select * unsinnig ist, select 1 reicht zu und kann u.U. erheblich performanter sein.

 

[datenbeisser]

Ohne dieses Exists würde JEDER Datensatz geändert. Ungeachtet dessen ob Tabelle_Neu eine passende ID enthält.
D. h. sollte es in Tabelle_Neu eine ID nicht geben, wird der Name auf NULL (also nichts) geändert.

Ja, genauso ist es:

UPDATE Table
SET
Name = (SELECT Table_Neu.Name_Neu FROM Table_Neu WHERE ID = Table.ID)

Damit werden ALLE Datenfelder "Name" auf Null gesetzt.

 

[datenbeisser]

Man sollte vielleicht noch erwähnen, daß das select * unsinnig ist, select 1 reicht zu und kann u.U. erheblich performanter sein.

Sorry, da stehe ich jetzt auf'm Schlauch. Die funktionierende Langform lautet:

UPDATE Table
SET
Name = (SELECT Table_Neu.Name_Neu FROM Table_Neu WHERE ID = Table.ID)
WHERE EXISTS (SELECT * FROM Table_Neu WHERE ID = Table.ID)

Und wie wird die verkürzte Form ohne "SELECT *" nun geschrieben: ?

 

[akretschmer]

Select * ist (in produktivem Code) immer ganz schlecht, siehe dazu auch hier:

https://www.pg-forum.de/viewtopic.php?f=66&t=4309

In dem Falle reicht es, select 1 zu machen.

test=*# insert into foo (name) values ('x');
INSERT 0 1   
Time: 0,383 ms   
test=*# select exists(select 1 from foo where name='x');   
 exists   
--------   
 t   
(1 row)

Stell Dir vor, Deine Tabelle Table_Neu enthält je Record ein binäres Feld mit 1GB Daten. Das würdest Du jedesmal selektieren und damit auch von der Platte kratzen, nur um es dann ungesehen in die Tonne zu werfen.

 

[datenbeisser]

Also mehr Performance könnte ich wirklich bei diesem Script benötigen. Denn die besagte Datenbank hat mehr als 1.2 Millionen Einträge. 60.000 Änderung darin via "UPDATE" dauerten 4 Stunden!

Zwei Fragen noch dazu:

1. Habe ich richtig verstanden, dass das schnellere Script dann lauten müsste:

UPDATE Table
SET
Name = (SELECT Table_Neu.Name_Neu FROM Table_Neu WHERE ID = Table.ID)
WHERE EXISTS (SELECT 1 FROM Table_Neu WHERE ID = Table.ID)

2. Mit "SELECT 1" wird also sichergestellt, dass kein vollständiger Datensatz mit allen Datenfeldern ausgewählt wird, wie das "SELECT *" tut. Was aber wählt "SELECT 1" aus? In "Table_Neu" existiert kein Datenfeld names "1". Ist "1" einfach eine "Dummy"-Platzhalter und es könnte auch "SELECT 6" oder "SELECT Y" geschrieben werden? Oder ist der "SELECT 1" Befehl genormt?

 

[akretschmer]

Ist "1" einfach eine "Dummy"-Platzhalter und es könnte auch "SELECT 6" oder "SELECT Y" geschrieben werden?

Ja.

 

[Distrilec]

Ja.

Jaein...
"Select Y" würde wahrscheinlich zu einem Syntaxfehler führen... Text muss in Quotes gesetzt werden -> "Select 'Y' "

Grundsätzlich ist es auch kein Dummy ...
Da es sich um eine feste Zahl handelt wird diese "berechnet" und nicht von deiner Platte gezogen (was eben wesentlich schneller ist...)

Das ganze funktioniert mit "Exists" nur so gut, da nicht auf Spaltenwerte geprüft wird...
"Exists" interessiert eigentlich nur eins: Ist mindestens eine Zeile im Resultset vorhanden oder eben nicht. Was da in der Zeile steht ist vollkommen egal

Man kann sogar (wörtlich) nichts selektieren:

Select Null From Table Where ...

Wobei dieses Nichts auch "berechnet" werden muss und es deswegen keinen wirklichen Performance-Vorteil bietet (im Vergleich zu 1)... Und 1 sieht dann einfach schöner aus...

 

[datenbeisser]

Danke für die Erklärung.

Habe es mal mit einer verkleinerten Form der Datenbank mit SQLite v3.8.10.2 getestet:

Update via - Exists mit Select * = 126194 ms
Update via - Exists mit Select 1 = 125998 ms

Macht also bei SQLite nur einen minimalen Unterschied aus. Vielleicht ist der Unterschied ja bei anderen Datenbanken wie MySQL oder anderen größer.

Danke an akretschmer und Distrilec für die Hilfe!

 

[Hony%]

60.000 Änderung darin via "UPDATE" dauerten 4 Stunden!

Das passiert wenn INSERT oder UPDATE über mehrere Datensätze ohne explizite Transaktion durchgeführt wird. Dann nämlich erzeugt SQLite für jeden Datensatz eine eigene Transaktion inklusive aller Prüfungen.

Total umständlich und weit weniger elegant und verständlich als Dein SQL Script. Aber damit müssen SQlite Programmierer anscheinend leben.

SQLite will auch nur ein fopen Ersatz sein. Deshalb werden Komfortfunktionen in der Regel nicht implementiert um den Umfang des Quellcodes sowie der erzeugten Binarys so gering wie möglich zu halten. Das DBMS selbst hat in kompilierter Form nicht einmal 500 kb.

 

[datenbeisser]

Danke Hony% für den Hinweis.

Als Anfänger habe ich "Transaction" bisher noch nicht genutzt. Verstehe ich richtig, dass mein Code dann MIT "Transaction" von SQLite wesentlich schneller ausgeführt werden sollte?:

BEGIN TRANSACTION
UPDATE Table
SET
Name = (SELECT Table_Neu.Name_Neu FROM Table_Neu WHERE ID = Table.ID)
WHERE EXISTS (SELECT 1 FROM Table_Neu WHERE ID = Table.ID)
END TRANSACTION