SQL abfrage hängt db auf

[Wowbagger]

hi leute,

kann sein, daß ich gerade ein massives brett vor'm kopf habe und der fehler peinlich ist, aber folgende kleine aktualisierungsabfrage bringt mein Access zum absturz:

UPDATE Prod, test_include SET Prod.Preis = [test_include]![Preis]
WHERE (((Prod.IdentNr) Like 'P_'+[test_include]![Ident]));

hier gibt es also eine tabelle "Prod", welche produkte enthält, deren preise aus einer tabelle "test_include" heraus aufgefrischt werden sollen. In "Prod" fangen alle IdentNr mit P_ and, in "test_include" hat man teilweise identische nummern, jedoch ohne das präfix P_
Die inhalte beider tabellen sind nicht identisch, d.h. in Prod gibt es IdentNr welche in "test_include" nicht vorkommen und umgekehrt, d.h. auch vollkommen andere nummernkreise (hersteller) welche auch garnicht upgedated werden sollen.

Imho sollte eigentlich alles funken, aber sobald ich die abfrage in Access anwerfe, gibt sich die db den goldenen schuß :((

Was läuft hier falsch? Die db enthält etwa 1900 einträge in "Prod" und weitere ca. 1400 in "test_include", aber das ist doch eigentlich nicht die leistungsgrenze (zumindest offline nicht)

/*,*/
Wowbagger

[Roman Pfarrhofer]

Hi Wowbagger!

tja - hätte mir gerne die db angesehen - aber da deine mail-addy nicht echt ist - hast du pech gehabt - muß jetzt in 7min. weg (heimat ich komme <freu/>)

hätte dir gerne geholfen nur mit deinem angaben ist das relativ schwierig...

CU Roman, der übers WE offline ist...

[Joerg Bencke]

hi leute,

Hi Wowbagger,

UPDATE Prod, test_include SET Prod.Preis = [test_include]![Preis]
WHERE (((Prod.IdentNr) Like 'P_'+[test_include]![Ident]));

das Problem liegt wahrscheinlich in dem Update von 2 Tabellen. (Hab kein Access hier)
In Oracle musst Du obiges übere in Join machen oder ueber ein Subquery. Aussedem solltest Du moeglichst = anstatt Like verwendern
(wenn es geht wg. Case Unterschieden etc, da like etwas langsamer sein soll.Gleichs gilt für die "exists" Funktion unten.)

Z.B: (Bei den String Funktionen bin ich mir nicht ganz sicher, in Oracle ist es "")
update prod set prod.preis = (select preis from test_include where prod.IdentNr = 'P' + test_include.Ident)
where exists (select Ident from test_include where prod.IdentNr = 'P' + test_include.Ident)

Um es direkter zu machen benotigtst Du Joins, aber die versuche ich in Updates imemr zu vermeiden ;-)

Viel Glueck, Joerg

[Uwe Nohl]

UPDATE Prod, test_include SET Prod.Preis = [test_include]![Preis]
WHERE (((Prod.IdentNr) Like 'P_'+[test_include]![Ident]));

Hallo Wowbagger,

Dem komischen SQL-statement (die lange Erklärung hätts nicht gebraucht) entnehme ich, dass Du
'Prod.Preis', also die Daten in der Spalte 'Preis' der Tabelle 'prod' aktualisieren willst.

Alle 'Preis'-Werte der Datensätze in 'Prod', deren 'IdentNr'-Werte einen um 'P_' ergänzten
'Ident'-Wert der Datensätze in Tabelle 'test_include' haben, sollen den 'Preis'-Wert dieser
Datensätze aus 'test_include' bekommen.

So habe _ich_ es verstanden, aber, nimm mirs nicht übel, an Deinem SQL verzweifelt jedes
DBMS. Komplizierte Aufgabe aber, zugegeben!

Hier (hoffentlich) die Hilfe:

a) kannst Du irgendeine hyperkomplizierte Aktualisierungsabfrage entwerfen (*schüttel*),
aber am besten aber ist es, sich für sowas gleich an VBA/DAO/ADO zu gewöhnen:

deshalb b) Öffne ein Modul und füge eine Funktion 'myfunction' ein (das von Access vorgeschlagene
'Public' kannst Du löschen, wenn Du die Funktion nur in einer DB brauchst):

--------------------------------
Function myfunction()
'DAO vorausgesetzt

Dim dbs As Database, rst As Recordset
Dim strSQL As String, i As Integer, j As Integer, st As String
Dim arr()
On Error GoTo ErrHandl

Set dbs = CurrentDb()

strSQL = "SELECT Ident, Preis FROM test_include;"

Set rst = dbs.OpenRecordset(strSQL, dbOpenSnapshot)
'nur auslesen, keine Aktualisierung.

arr = rst.GetRows(rst.RecordCount)
'ins array reinziehen
rst.Close
Set rst = nothing 'object abmelden

For i = 0 Ubound(arr,2)
arr(0,i) = "P_" & arr(0,i)
Next
'anpassen

'nächster SQL-Schritt, diesmal aktualisierbar,
strSQL = "SELECT IdentNr, Preis FROM prod;"

Set rst = dbs.OpenRecordset(strSQL, dbOpenDynaset)
'object neu anmelden, dynaset = aktualisierbar!
rst.MoveFirst

For i = 0 To Ubound(arr,2)
For j = 0 To rst.RecordCount - 1
  If rst![IdentNr] = arr(0,i) Then
   rst.Edit
   rst![Preis] = arr(1,i)
   rst.Update
  End If
  rst.MoveNext
Next
Next

rst.Close
Set rst = nothing
db.Close
Set dbs = nothing

Exit Function

ErrHandl:
MsgBox Err.Description
rst.Close
Set rst = nothing
dbs.Close
Set dbs = nothing

End Function
----------------------------------

Aufruf über ein Formular 'myform'->Entwurf->Befehlsschaltfläche->Eigenschaften->Beim Klicken:
=myfunction()
----------------------------------

Falls Du ADO (in Acc2K möglich) benutzt, musst Du nur die Instanziierung des Recordsetobjektes
anpassen und vorher statt des Datenbankobjektes ein Verbindungsobjekt instanziieren.

Die db enthält etwa 1900 einträge in "Prod" und weitere ca. 1400
in "test_include", aber das ist doch eigentlich nicht die leistungsgrenze
(zumindest offline nicht)

Access verkraftet locker mehr. Ich habe eine DB mit bald 700.000 Records in der dicksten
Tabelle. Insgesamt habe ich ca. 30 Tabellen, die meisten so von 10.000 bis 50.000 Records.
Alles funzt. Nur schreibe bloss nicht mehr so ein komisches SQL, denn dann saufen alle DB-Systeme
auch schon bei 2 Records ab.

Gruss
Uwe Nohl

[Uwe Nohl]

Hallo,
ich nochmal

Set rst = dbs.OpenRecordset(strSQL, dbOpenDynaset)

»»  'object neu anmelden, dynaset = aktualisierbar!

Könnte sein, dass hier erst ein

rst.MoveLast

stehen muss, damit korrekt gezählt wird

Gruss
Uwe Nohl

[Uwe Nohl]

Hallo,
ich nochmal
Es sind leider Fehler in der Funktion drin. Hier die korrekte Version:
Function myfunction()
'DAO vorausgesetzt

Dim dbs As Database, rst As Recordset
Dim strSQL As String, i As Integer, j As Integer, st As String
Dim arr()
On Error GoTo ErrHandl

Set dbs = CurrentDb()

strSQL = "SELECT Ident, Preis FROM test_include;"

Set rst = dbs.OpenRecordset(strSQL, dbOpenSnapshot)
'nur auslesen, keine Aktualisierung.

arr = rst.GetRows(rst.RecordCount)
'ins array reinziehen
rst.Close
Set rst = nothing 'object abmelden

For i = 0 Ubound(arr,2)
arr(0,i) = "P_" & arr(0,i)
Next
'anpassen

'nächster SQL-Schritt, diesmal aktualisierbar,
strSQL = "SELECT IdentNr, Preis FROM prod;"

Set rst = dbs.OpenRecordset(strSQL, dbOpenDynaset)
'object neu anmelden, dynaset = aktualisierbar!
rst.MoveLast
rst.MoveFirst

For i = 0 To Ubound(arr,2)
For j = 0 To rst.RecordCount - 1
  If rst![IdentNr] = arr(0,i) Then
   rst.Edit
   rst![Preis] = arr(1,i)
   rst.Update
   Exit For
  End If
  rst.MoveNext
Next
rst.MoveFirst
Next

rst.Close
Set rst = nothing
db.Close
Set dbs = nothing

Exit Function

ErrHandl:
MsgBox Err.Description
rst.Close
Set rst = nothing
dbs.Close
Set dbs = nothing

End Function
Gruss
Uwe Nohl