Update Statement with SubSelects

[Techno_Dex]

I have a record in a table that I would like to Update from one status to
another status. The problem is the record I want to update contains the
StatusID where as I only know the StatusValue from the Status Table and the
GrpName from the Doc table. I've been racking my head to get this figured
out but have had no luck so far. Any help would be much appriciated

I have tried various reworkings but here is what I have come up with so far.
(By the way, this works in TSQL on MSSQL2000). Am I missing something about
the limitations that MSAccess JET Engine has on using SubSelects or Inner
Joins?????

UPDATE Doc D
SET D.StatusID=(SELECT ID
FROM Status
WHERE StatusValue='Retired')
WHERE D.ID=(SELECT D2.ID
FROM Doc D2
INNER JOIN Status S2
ON D2.StatusID=S2.ID
WHERE D2.GrpID='AAA' AND S2.Status='Active')

Tables

Table: Doc
ID | GrpName | StatusID | Version
------------------------------------------
1 | AAA | 2 | 1
2 | AAA | 2 | 2
3 | AAA | 1 | 3 <-- Record I want to
Update. I want the new StatusID to be 2 (Retired)
4 | BBB | 2 | 1
5 | BBB | 1 | 2

Table:Status
ID | StatusValue

 

[John Spencer]

Although you expect this
(SELECT ID
FROM Status
WHERE StatusValue='Retired')
to return one value, Access Jet believes it is possible it will return more
than one value and therefore will not allow the query to run. MS SQL will
run the query and throw an error (IIRC) if more than one value is returned.

You may be able to rewrite this in Access by using something like the
following. TEST this on a backup copy.

UPDATE Doc as D INNER JOIN Status as S
ON D.StatusID = S.ID
SET D.StatusID = .StatusValue
WHERE S.StatusValue = "Retired"
AND D.GroupID = "AAA"
AND S.Status = "Active"

 

[SteveS]

Hi Techno_Dex,

Using a subquery in the SET clause makes the query non-updatable

In the second subquery, you have:

WHERE D2.GrpID='AAA' AND S2.Status='Active')

The DOC (alias D2) table does not have a field named "GrpID" and the STATUS
table does not have a field named "Status".

If you know that you want the field to be updated to "Retired" (ID=2), why
don't you just use the value 2? Then the query becomes updatable (after you
change the field names in the Where clause).


This query is not very useful with the hard coded values. If you are using
controls on a form, the query is easier to write. Say you have a form named
"frmChange" and you have three combo boxes "cboSetStatus", "cboSelectGrp" and
"cboSelectStatus".

The SQL of the query would look like:

UPDATE Doc SET Doc.StatusID = [forms]![frmChange].[cboSetStatus]
WHERE (( Doc.[grpname]=[forms]![frmChange].[cboSelectGrp] And
Doc.[statusid]=[forms]![frmChange].[cboSelectStatus]));


Using your table examples, the combo boxes properties would be

cboSetStatus & cboSelectStatus
RowSource: SELECT Status.ID, Status.StatusValue FROM Status;
Bound colume = 1
Column count = 2
Col widths = 0


cboSelectGrp
RowSource: SELECT DISTINCT Doc.GrpName FROM Doc;
Bound colume = 1
Column count = 1


Another way would be use VBA. Using the example form and controls above, the
sub would look like: (watch for line wrap)


Public Sub SetTable(pStatusID As Long, pGrpName As String, pStatusValue As
Long)
Dim strSQL As String

strSQL = "UPDATE Doc D"
strSQL = strSQL & " SET D.StatusID= " & pStatusID
strSQL = strSQL & " WHERE D.ID=(SELECT D2.ID"
strSQL = strSQL & " FROM Doc D2 INNER JOIN Status S2 ON D2.StatusID=S2.ID"
strSQL = strSQL & " WHERE D2.GrpName='" & pGrpName & "' AND S2.id =" &
pStatusValue & ")"

Debug.Print strSQL

CurrentDb.Execute strSQL, dbfailonerror

End Sub


The code for a buttton to run the code:

Public Sub Command0_Click()
Call SetTable(cboSetStatus, cboSelectGrp, cboSelectStatus)
End Sub



HTH

 

[SteveS]

Oops,

Don't forbet to delete or comment out the line

Debug.Print strSQL



--
Steve S
--------------------------------
"Veni, Vidi, Velcro"
(I came; I saw; I stuck around.)

Hi Techno_Dex,

Using a subquery in the SET clause makes the query non-updatable

In the second subquery, you have:

WHERE D2.GrpID='AAA' AND S2.Status='Active')

The DOC (alias D2) table does not have a field named "GrpID" and the STATUS
table does not have a field named "Status".

If you know that you want the field to be updated to "Retired" (ID=2), why
don't you just use the value 2? Then the query becomes updatable (after you
change the field names in the Where clause).


This query is not very useful with the hard coded values. If you are using
controls on a form, the query is easier to write. Say you have a form named
"frmChange" and you have three combo boxes "cboSetStatus", "cboSelectGrp" and
"cboSelectStatus".

The SQL of the query would look like:

UPDATE Doc SET Doc.StatusID = [forms]![frmChange].[cboSetStatus]
WHERE (( Doc.[grpname]=[forms]![frmChange].[cboSelectGrp] And
Doc.[statusid]=[forms]![frmChange].[cboSelectStatus]));


Using your table examples, the combo boxes properties would be

cboSetStatus & cboSelectStatus
RowSource: SELECT Status.ID, Status.StatusValue FROM Status;
Bound colume = 1
Column count = 2
Col widths = 0


cboSelectGrp
RowSource: SELECT DISTINCT Doc.GrpName FROM Doc;
Bound colume = 1
Column count = 1


Another way would be use VBA. Using the example form and controls above, the
sub would look like: (watch for line wrap)


Public Sub SetTable(pStatusID As Long, pGrpName As String, pStatusValue As
Long)
Dim strSQL As String

strSQL = "UPDATE Doc D"
strSQL = strSQL & " SET D.StatusID= " & pStatusID
strSQL = strSQL & " WHERE D.ID=(SELECT D2.ID"
strSQL = strSQL & " FROM Doc D2 INNER JOIN Status S2 ON D2.StatusID=S2.ID"
strSQL = strSQL & " WHERE D2.GrpName='" & pGrpName & "' AND S2.id =" &
pStatusValue & ")"

Debug.Print strSQL

CurrentDb.Execute strSQL, dbfailonerror

End Sub


The code for a buttton to run the code:

Public Sub Command0_Click()
Call SetTable(cboSetStatus, cboSelectGrp, cboSelectStatus)
End Sub



HTH
--
Steve S
--------------------------------
"Veni, Vidi, Velcro"
(I came; I saw; I stuck around.)

I have a record in a table that I would like to Update from one status to
another status. The problem is the record I want to update contains the
StatusID where as I only know the StatusValue from the Status Table and the
GrpName from the Doc table. I've been racking my head to get this figured
out but have had no luck so far. Any help would be much appriciated

I have tried various reworkings but here is what I have come up with so far.
(By the way, this works in TSQL on MSSQL2000). Am I missing something about
the limitations that MSAccess JET Engine has on using SubSelects or Inner
Joins?????

UPDATE Doc D
SET D.StatusID=(SELECT ID
FROM Status
WHERE StatusValue='Retired')
WHERE D.ID=(SELECT D2.ID
FROM Doc D2
INNER JOIN Status S2
ON D2.StatusID=S2.ID
WHERE D2.GrpID='AAA' AND S2.Status='Active')

Tables

Table: Doc
ID | GrpName | StatusID | Version
------------------------------------------
1 | AAA | 2 | 1
2 | AAA | 2 | 2
3 | AAA | 1 | 3 <-- Record I want to
Update. I want the new StatusID to be 2 (Retired)
4 | BBB | 2 | 1
5 | BBB | 1 | 2

Table:Status
ID | StatusValue