[SQL] Update mit JOIN

  • Themenstarter Themenstarter Gelöschtes Mitglied 16444
  • Beginndatum Beginndatum
G

Gelöschtes Mitglied 16444

Guest
Moin HaBo,

ich habe eine Tabelle goods, in der Artikel aufgelistet sind. Zusätzlich habe ich eine Tabelle storage, wo aufgelistet wird, wo diese Güter gelagert sind und wie deren Preis dort ist.
Nun gibt es auch gebrauchte Güter, die quasi in der goods Tabelle doppelt vorkommen und sich nur im Namen durch ein vorangestelltes 'used ' unterscheiden.
Jetzt möchte ich aber in der storage Tabelle die item_id aller gebrauchten Produkte auf die des neuwertigen Produktes ändern und zusätzlich den Preis (der zur Zeit noch der des neuwertigen Artikels ist) auf 40% reduzieren.

Code: 
UPDATE storage
JOIN goods used ON ( storage.itemId = used.item_id )
JOIN goods ON ( goods.name = SUBSTRING( used.name, 5 ) )
SET itemId = goods.item_id, price = price * 0.4

Bei der Ausführung gibt es allerdings Probleme:

Code: 
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

Jemand einen Tipp, wie ich die Ausführungszeit des Querrys verkürzen kann?
Die Tabelle goods hat ~3000 Zeilen und storage ~36000 Zeilen.
 
Original von Thunderb0lt
Moin HaBo,

ich habe eine Tabelle goods, in der Artikel aufgelistet sind. Zusätzlich habe ich eine Tabelle storage, wo aufgelistet wird, wo diese Güter gelagert sind und wie deren Preis dort ist.
Nun gibt es auch gebrauchte Güter, die quasi in der goods Tabelle doppelt vorkommen und sich nur im Namen durch ein vorangestelltes 'used ' unterscheiden.
Jetzt möchte ich aber in der storage Tabelle die item_id aller gebrauchten Produkte auf die des neuwertigen Produktes ändern und zusätzlich den Preis (der zur Zeit noch der des neuwertigen Artikels ist) auf 40% reduzieren.

Code: 
UPDATE storage
JOIN goods used ON ( storage.itemId = used.item_id )
JOIN goods ON ( goods.name = SUBSTRING( used.name, 5 ) )
SET itemId = goods.item_id, price = price * 0.4
Zum Vergrößern anklicken....

Hallo Thunderbolt,

ich kann aus Deinen Zeilen nicht lesen, was Du genau machen willst. z.B. wie Dein ER Modell aussieht, warum Du die ids ändern willst. 36.000 Zeilen sind garnichts, da darf es keine Performanceprobleme geben. Außerdem hab ich wirklich keine Ahnung mit welchen DBMS so ein Statement funktioniert. Nach ansi sql solltest Du folgende Syntax verwenden.

UPDATE <Tabelle | View>
SET <Name einer Spalte> = <Ausdruck aus Spalten,
Konstanten, Funktionen>
[, weitere Spaltennamen = Ausdruck]
[FROM <Tabelle> [INNER | LEFT | RIGHT] JOIN <Tabelle>
ON <Spalte-1 = Spalte-2>]
WHERE <Bedingung>
 
Das DBMS ist MySQL. Ich habe im Netzt an verschiedenen Stellen die von mir verwendete Syntax gesehen und es scheint ja auch zu funktionieren, ein Syntax Fehler sollte ja auch recht schnell geschmiessen werden und nicht erst nach einiger Zeit und dann durch die obige Fehlermeldung, oder?

Was ich machen will, hmm wie soll ich das anders erklären als oben :)

Ich habe eine Zeile in der Tabelle 'goods' diese Zeile unterscheidet sich durch die ID und den Namen von einer zweiten Zeile darin. Die beiden Namen sind z.B. 'chair' und 'used chair'. In der Tabelle 'storage' befindet sich nun eine Zeile mit Fremdschlüsselbezug auf 'goods' - in dem Fall eben 'used chair'. Ich möchte jetzt diesen Fremdschlüssel durch den für 'chair' ersetzen und noch eine Weitere Zelle modifizieren.

Es ist im übrigen kein FK der im DBMS hinterlegt ist. Dahingehend muss also nichts beachtet werden.
 
Zwei Möglichkeiten wären WHERE und/oder LIMIT - hat aber zu Folge, dass das Update in mehreren Schritten erfolgen muss.
 
Das Problem tritt auf zwei verschiedenen Systemen mit verschiedenen Versionen auf.
Es gibt wohl auch eine entsprechende Configvariable, die das Timeout steuert, aber die zu ändern wollte ich vermeiden, da ich dachte, dass man das Querry an sich vielleicht noch durch Modifikationen optimieren kann.

Ein LIMIT in einer UPDATE-Klausel mit mehreren Tabellen ist im übrigen nicht erlaubt.
 
Thunderbolt
UPDATE storage
JOIN goods used ON ( storage.itemId = used.item_id )
JOIN goods ON ( goods.name = SUBSTRING( used.name, 5 ) )
SET itemId = goods.item_id, price = price * 0.4
Zum Vergrößern anklicken....

Hi,
mag sein dass dieses merkwürdige Statement auf mysql ausgeführt werden kann.
Aber die Zeile:
JOIN goods ON ( goods.name = SUBSTRING( used.name, 5 ) )
kann niemals wahr sein.[EDIT] same as 1=5
Dennoch befürchte ich, dass es Mängel im Datenbankdesign gibt.
Das mit dem "used" string im Namen widerspricht bereits der 1. Normalform im Bereich Atomarität.
 
Warum sollte dieser Teil des Querys nie wahr sein? Ich kann dir versichern, dass eine SELECT-Abfrage mit den Selben JOINs funktioniert ;)
Es macht einen JOIN auf die Tabelle goods und anschließend nocheinmal einen JOIN auf die gleiche Tabelle. Also im Endeffekt quasi einen self join und der funktioniert an anderer Stelle ja auch.

Ob das Datenbankdesign nun die 1. Normalform verletzt oder nicht sei jetzt mal dahingestellt, an anderer Stelle wäre ich für einen solchen Hinweis auch dankbar gewesen, aber in diesem Fall stammt das Design nicht von mir und ich habe auch keine Möglichkeit es zu ändern.
 
Hallo Thunderbolt,

kannst Du mal Beispielzeilen posten, die in den Tabellen drinne sind ?
Du joinst
itemId = goods.item_id
, dass bedeuted die sind gleich, und dann setzt du sie gleich ?
SET storage.id = goods.id

Ich meine, wenn ich zu unrecht Zweifel an dem Datenbankkonstrukt und der Abfrage an sich habe, bitte belehrt mich....
 
Ich geb auch nochmal meinen Senf dazu:
Ich persönlich finde das ganze Konstrukt etwas sehr merkwürdig. Ich behaupte mal das ist nicht SQL-Standard, zumindest hab ich sowas noch nie gesehen und ich hab schon verdammt viele SQL-Statements gelesen. Zieh doch das ganze in 2. Statements auseinander. Ich behaupte mal daß das ohne Probleme funktionieren wird und es versteht dann auch jeder. Die Kunst im Programmieren (SQL zähl ich da mal dazu) liegt darin etwas so zu schreiben, daß es ein anderer auch wieder versteht und nicht mit allen nur möglichen Tricks das Ziel erreicht wird.
Im Übrigen schadet es deinem Konstrukt mit Sicherheit auch nicht wenn du die Join-Aliases auch im SET benutzt.
Heißt die Spalte itemId wirklich in der einen Tabelle itemId und in der anderen item_id? Wenn ja ists nicht verkehrt aber verdammt hässlich.
 
Ich kram das hier mal wieder raus.

Ja, das ist häßlich, und ich kann dir sagen, das ist noch nichtmal das häßlichste an der ganzen Datenbank ;)
Aber wie bereits erwähnt muss ich mit dem arbeiten, was da ist und habe nicht die Möglichkeit an der Struktur etwas zu ändern.

Das ganze in einem Query zu erledigen soll der Sinn hinter der ganzen Geschichte sein.

Code: 
SELECT goods.item_id AS used_item_id, g.item_id
FROM goods, goods g
WHERE goods.name REGEXP('used ')
AND g.name = SUBSTRING(goods.name, 5)

UPDATE storage
SET itemID = $item_id$, price=price*0.04
WHERE itemID = $used_item_id$
 
Mach 2 Queries und gut ists. So würde es jeder normale Mensch machen. 2 Queries sollte man in jeder Programmier-/Skriptsprache hintereinander abschicken können. Das hat nichts mit Struktur sondern mit der Fähigkeit eines Programmierers und gesunden Menschenverstand zu tun.
 
Ich kann an der Stelle aber nur mit der Datenbank direkt reden.

Und bitte unterlasse doch solche persönlichen Anfeindungen. Du kannst gerne die Sache kritisieren und sagen, dass du von ihr nichts hälst, aber mich brauchst du deswegen ja nicht angreifen.
 
Um antworten zu können musst du eingeloggt sein.
Zurück
Oben