Update mit Abfrageergebnis

[marcus]

Guten Tag,

ich bin eben auf der Suche nach einem Weg in einer Updateabfrage das Ergebnis einer Abfrage einzubauen, vom Typ:

UPDATE Tabelle SET Feld="Abfrageergebnis" WHERE ID=1

Abfrageergebnis sollte sowas sein wie:

SELECT Sum(Feld) FROM Tabelle WHERE ID < x AND > y

Kann mir jemand sagen wie ich die verbinden kann. Funktionen sind leider raus. Muss mit Access arbeiten,...

Vielen Dank,
Marcus

[Vinzenz Mai]

Hallo

ich bin eben auf der Suche nach einem Weg in einer Updateabfrage das Ergebnis einer Abfrage einzubauen, vom Typ:

UPDATE Tabelle SET Feld="Abfrageergebnis" WHERE ID=1
Abfrageergebnis sollte sowas sein wie:

SELECT Sum(Feld) FROM Tabelle WHERE ID < x AND > y

Letztere Anweisung ist fehlerhaft, die WHERE-Klausel müsste lauten

WHERE (ID < x) AND (ID > y)

Du könntest die WHERE-Klausel umschreiben, so dass Du BETWEEN verwenden kannst, JetSQL (der SQL-Dialekt der Jet-Engine, dem DBMS hinter MS Access) kennt BETWEEN, genauer gesagt kann JetSQL sich diesen Ausdruck über den Ausdruck-Interpreter von VBA auswerten lassen.

Langsam und zum Mitschreiben: Ist "Tabelle" hier nur ein Platzhalter oder willst Du die gleiche Tabelle updaten, aus der Du auswählst. Letzteres geht bei den meisten mir bekannten Datenbankmanagementsystemen nicht direkt.

Am besten gibst Du ein paar Beispieldatensätze Deiner Tabellen an und das von Dir gewünschte Ergebnis.

Kann mir jemand sagen wie ich die verbinden kann. Funktionen sind leider raus. Muss mit Access arbeiten,...

MS Access kennt zwar keine Stored Procedures, dafür aber Views (die heißen dort "Auswahlabfragen") und kann mit Subselects umgehen.

Freundliche Grüße

Vinzenz

[Marcus]

Der Tipp mit BETWEEN ist schon mal gut, danke.

Es handelt sich dabei um dieselbe Tabelle. Sprich:

UPDATE Tabelle1 SET wert="SELECT Sum(wert) FROM Tabelle WHERE ID BETWEEN 1 AND 3" WHERE ID=4

ID     wert
Record 1:   1       10
Record 2:   2        5
Record 3:   3        1

Ziel:
Record 3:   4       16

Danke, Marcus

[Vinzenz Mai]

Hallo Marcus,

Der Tipp mit BETWEEN ist schon mal gut, danke.

bitteschön.

Es handelt sich dabei um dieselbe Tabelle. Sprich:

dann befürchte ich, dass MS Access Dir das nicht in einer SQL-Anweisung ermöglicht :-( Dies gilt für andere mir bekannte DBMS übrigens auch. Was spricht dagegen, die Aufgabe in zwei getrennten Statements vorzunehmen, die Du in der API in einer Transaktion kapselst?

Freundliche Grüße

Vinzenz

[Marcus]

Bloede Frage, wie geht das. Habs noch nicht verstanden.

[Vinzenz Mai]

Hallo Marcus,

Bloede Frage, wie geht das. Habs noch nicht verstanden.

dazu müsste ich zuerst wissen, mit welcher Programmiersprache Du die Access-Datenbank zugreifst.

Freundliche Grüße

Vinzenz

[Marcus]

Ich schreib ne kleine Access DB, also mit VBA in Access.

[Vinzenz Mai]

Hallo Marcus,

Ich schreib ne kleine Access DB, also mit VBA in Access.

dann kann ich Dir die Online-Hilfe von Access ans Herz legen :-)
Nun ein Minitutorial für diese Aufgabe, ohne irgendwelche Fehlerbehandlung - nur für den Einstiege in den Umgang mit VBA und ADO (Microsoft ActiveX Data Objects).
Die Fehlerbehandlung überlasse ich Dir zur Übung ;-)

1. Schritt: Wie führe ich eine UPDATE-SQL-Anweisung aus

Prinzipiell lautet Deine Update-Anweisung:

UPDATE Tabelle1  
    SET wert = 16  
WHERE ID = 4

Mit VBA kannst Du dies wie folgt lösen:

' Lege eine entsprechende Zeichenkettenvariable an
Dim sqlUpdate As String
Dim intAnzahlZeilen As Integer            ' für die Anzahl der Zeilen
' Weise der Variablen das SQL-Statement zu
' Hinweise: vbCrLf ist eine VB-Konstante für einen Zeilenumbruch
'           Der Unterstrich dient als Zeichenfortsetzungszeichen,
'           d.h. die Anweisung wird in der folgenden Zeile fortgesetzt
'           Somit kannst Du übersichtlichere Anweisungen schreiben wie
'           ich sie von anderen Programmiersprachen her gewohnt bin.
'           Die Anzahl der Zeilenfortsetzungen ist allerdings begrenzt
'           soweit ich mich erinnere auf insgesamt 16 Zeilen
sqlUpdate = _
    "UPDATE Tabelle1" & vbCrLf & _
        "SET wert = 16" & vbCrLf & _
    "WHERE ID = 4"

' Führe das SQL-Statement aus
' Benutze dazu das Verbindungsobjekt der aktuellen MDB-Datei
'    currentProject.Connection
' Beachte, dass eine Update-Anweisung keine Ergebnismenge zurückliefert
' Im ersten (optionalen) Parameter kannst Du Dir anzeigen lassen, wieviele
' Zeilen von Deinem Statement betroffen waren
CurrentProject.Connection.Execute sqlUpdate intAnzahlZeilen

' Gebe die Anzahl der geänderten Zeilen im Direktfenster aus
Debug.Print intAnzahlZeilen      ' wirklich nur für Debug-Zwecke :-)

2. Schritt: Parametrisiere die Abfrage

Die Abfrage wird bestimmt mit unterschiedlichen Zielwerten ausgeführt und für verschiedene Datensätze. Daher bietet es sich an diese Anweisung zu parametrisieren. Wir benötigen dazu ein ADODB.Command-Objekt und zwei ADODB.Parameter-Objekte. Es sieht komplizierter aus als es ist:

Dim objUpdate As New ADODB.Command
Dim objParamsWert As New ADODB.Parameter      ' für den Parameter Wert
Dim objParamsID As New ADODB.Parameter        ' für den Parameter ID
Dim intAnzahlZeilen As Integer            ' für die Anzahl der Zeilen

' Weise dem Command-Objekt die SQL-Anweisung zu
' Das Fragezeichen ? kennzeichnet Parameter der Anweisung
objUpdate.CommandText = "UPDATE Tabelle1" & vbCrLf & _
        "SET wert = ? " & vbCrLf & _
        "WHERE ID = ?"

' Erzeuge ein Parameter-Objekt für den Paramter "Wert" mit dem Inhalt 16
Set objParamsWert = objUpdate.CreateParameter("Wert", adBigInt, , , 16)

' Hänge dieses Objekt in die Liste der Parameter-Objekte des Command-Objektes
objUpdate.Parameters.Append objParamsWert

' Gehe analog mit dem Parameter ID vor
Set objParamsID = objUpdate.CreateParameter("ID", adBigInt, , , 4)
objUpdate.Parameters.Append objParamsID

' Weise die aktuelle Verbindung der MDB dem Command-Objekt als Connection zu:
Set objUpdate.ActiveConnection = CurrentProject.Connection
' und führe das Command-Objekt aus
objUpdate.Execute intAnzahlZeilen

' Die Debug-Ausgabe sollte gleich bleiben
Debug.Print intAnzahlZeilen

' Ein paar Aufräumarbeiten zum Abschluß
Set objUpdate = Nothing
Set objParamsWert = Nothing
Set objParamsID = Nothing

3. Schritt - Übernehme den Wert des Parameters "Wert" aus einer Abfrage

Dim objSum As New ADODB.Command
Dim objUpdate As New ADODB.Command
Dim adoRS As ADODB.Recordset
Dim objParamsMinID As ADODB.Parameter
Dim objParamsMaxID As ADODB.Parameter
Dim objParamsWert As ADODB.Parameter
Dim objParamsID As ADODB.Parameter
Dim intAnzahlZeilen As Integer
Dim wert as Long

' Ermittle den neuen Wert
' Ebenfalls eine parametrisierte Abfrage
objSum.CommandText = "SELECT SUM(wert) AS wert" & vbCrLf & _
    "FROM Tabelle1" & vbCrLf & _
    "WHERE ID BETWEEN ? AND ?;"

' Das kennen wir schon: Parameter-Objekt erzeugen und anhängen
Set objParamsMinID = objSum.CreateParameter("MinID", adBigInt, , , 1)
objSum.Parameters.Append objParamsMinID

' für die obere Grenze analog
Set objParamsMaxID = objSum.CreateParameter("MaxID", adBigInt, , , 3)
objSum.Parameters.Append objParamsMaxID

' Verbindung zuweisen
Set objSum.ActiveConnection = CurrentProject.Connection

' Diese Abfrage sollte genau eine Zeile zurückliefern
' Hier ist definitiv eine Fehlerbehandlung erforderlich!
' Wie gesagt, Dir zur Übung überlassen
Set adoRS = objSum.Execute()

' Ich gehe unverschämterweise einfach zum ersten Datensatz.
' Bitte überprüfe, ob dieser überhaupt vorhanden ist!
' Ansonsten kann dies einen Laufzeitfehler verursachen
' Laufzeitfehler sind zu behandeln, wie Du das angehen kannst,
' steht in der Online-Hilfe
adoRS.MoveFirst

' Schauen wir uns den Wert in der Spalte "wert" an :-)
wert = adoRS.Fields("wert")
' und geben den Wert zu Debug-Zwecken aus
Debug.Print wert

' Nun bauen wir das Update-Statement wie vorher parametrisiert zusammen
objUpdate.CommandText = "UPDATE Tabelle1" & vbCrLf & _
    "SET wert = ? " & vbCrLf & _
    "WHERE ID = ?;"

' und statt hartcodierter 16 übergeben wir den Wert, den wir in der ersten
' Anweisung ermittelt haben
Set objParamsWert = objUpdate.CreateParameter("Wert", adBigInt, , ,wert))
objUpdate.Parameters.Append objParamsWert
' Die ID setzen wir analog zu Schritt 2
Set objParamsID = objUpdate.CreateParameter("ID", adBigInt, , , 4)
objUpdate.Parameters.Append objParamsID

' Führen die Update-Anweisung aus
Set objUpdate.ActiveConnection = CurrentProject.Connection
objUpdate.Execute intAnzahlZeilen

' und schauen uns an, wieviele Zeilen geändert wurden
Debug.Print intAnzahlZeilen

' Nicht vergessen: Aufräumarbeiten
Set adoRS = Nothing
Set objSum = Nothing
Set objParamsMinID = Nothing
Set objParamsMaxID = Nothing
Set objUpdate = Nothing
Set objParamsWert = Nothing
Set objParamsID = Nothing

4. Schritt - Packe das ganze in eine Prozedur, der die Parameter
   - MinID, MaxID und ID übergeben werden

Public Sub UpdateWert( _
    ByVal lngMinID As Long, _
    ByVal lngMaxID As Long, _
    ByVal lngID As Long _
)
    Dim objSum As New ADODB.Command
    Dim objUpdate As New ADODB.Command
    Dim adoRS As ADODB.Recordset
    Dim objParamsMinID As New ADODB.Parameter
    Dim objParamsMaxID As New ADODB.Parameter
    Dim objParamsWert As New ADODB.Parameter
    Dim objParamsID As New ADODB.Parameter

' Ermittle den neuen Wert
    objSum.CommandText = "SELECT SUM(wert) AS wert" & vbCrLf & _
        "FROM Tabelle1" & vbCrLf & _
        "WHERE ID BETWEEN ? AND ?;"

' Statt der hartcodierten 1 bzw 3 die Variablen aus den Übergabeparameter
    Set objParamsMinID = objSum.CreateParameter("MinID", adBigInt, , , lngMinID)
    objSum.Parameters.Append objParamsMinID
    Set objParamsMaxID = objSum.CreateParameter("MaxID", adBigInt, , , lngMaxID)
    objSum.Parameters.Append objParamsMaxID

Set objSum.ActiveConnection = CurrentProject.Connection
    Set adoRS = objSum.Execute()
    ' Vergiß nicht die Fehlerbehandlung!
    adoRS.MoveFirst

objUpdate.CommandText = "UPDATE Tabelle1" & vbCrLf & _
        "SET wert = ? " & vbCrLf & _
        "WHERE ID = ?;"

' Wir verzichten auf die Zuweisung zu einer Variablen,
    ' übergeben den Wert direkt an das Parameter-Objekt
    ' und verzichten auch auf alle Debug-Ausgaben
    Set objParamsWert = objUpdate.CreateParameter("Wert", adBigInt, , , adoRS.Fields("wert"))
    objUpdate.Parameters.Append objParamsWert
    Set objParamsID = objUpdate.CreateParameter("ID", adBigInt, , , lngID)
    objUpdate.Parameters.Append objParamsID

Set objUpdate.ActiveConnection = CurrentProject.Connection
    objUpdate.Execute

Set adoRS = Nothing
    Set objSum = Nothing
    Set objParamsMinID = Nothing
    Set objParamsMaxID = Nothing
    Set objUpdate = Nothing
    Set objParamsWert = Nothing
    Set objParamsID = Nothing

End Sub

Nun kannst Du an geeigneter Stelle diese Prozedur mit den notwendigen Parametern aufrufen. Beispiel:

' Beachte: Bei Prozeduraufrufen wird die Parameterliste _nicht_ in Klammern eingeschlossen.
UpdateWert 1, 3, 4

Beachte, dass Du Dich um eine Fehlerbehandlung kümmern musst. Beachte, dass Du im Fall einer Mehrbenutzerumgebung damit rechnen musst, dass in der Zwischenzeit Daten geändert werden können, weshalb Du zu einer Sperrstrategie greifen solltest. Studiere die Onlinehilfe. Besorge Dir ein vernünftiges Buch :-)

Freundliche Grüße

Vinzenz