UPDATE (SQL): multiple SET fields not allowed?

[Allen_N]

I'm trying to change 2 fields in Table1 based on a list of supersessions in
Table2, but I get a syntax error when I try to implement the following SQL
statement in the 'SQL View' window:

UPDATE Table1 a
SET (Grp1, Item1) =
(SELECT
Grp2new,
Item2new
FROM Table2 b
WHERE a.Grp1 = b.Grp2old AND a.Item1 = b.Item2old);

Access doesn't like the "(" after the SET. But, it also complains at the
first "," if I omit the parentheses. Does Access simply not allow multi-field
UPDATE queries?

Thanks!

 

[Duane Hookom]

Set(s) must be
FieldA = ValueA,
FieldB = ValueB,
FieldC = ValueC,
....

You can use INSERT INTO table1 (FieldA, FieldB, FieldC,...)

 

[Allen Browne]

Updating multiple fields is not a problem, but returning multiple fields
from a subquery is.

 

[Allen_N]

Thanks, Allen.

I tried splitting it into 2 queries, starting with:

UPDATE Table1 a
SET Grp1 =
(SELECT
Grp2new
FROM Table2 b
WHERE a.Grp1 = b.Grp2old AND a.Item1 = b.Item2old);

.... but now I get:

"Run-time error '3073': Operation must be an updateable query."

The table was opened as "acEdit", so I can't see what it doesn't like.

 

[Allen Browne]

Any chance of using an INNER JOIN between Table1 and Table2?

Join on the 2 fields, and the query should be updatable.

 

[Allen_N]

Any chance of using an INNER JOIN between Table1 and Table2?

Join on the 2 fields, and the query should be updatable.

 

[Allen_N]

(My previous reply didn't contain what I typed! Data lost due to timeout?)

No, I can't see that working. I'm now trying to do it by stepping through
the records of Table1 in a VB loop. What syntax can I use to change the value
of a field in the current record?

 

[Duane Hookom]

This would run very slow with lots of records but might work. This assumes
all fields are numeric. You would need to add a bunch of quotes of the
fields are text.

UPDATE Table1
SET Grp1 =
DLookup("Grp2new", "Table2","Grp2old =" & Grp1 & " AND Item2old = " &
Item1 );

 

[Allen_N]

Execution speed is nothing compared to the time this program has already
wasted. I only need to build the new table once, then use it as input to
another project.

I still need to change 2 fields at once, based on the values of 2 pairs of
corresponding fields in the index of replacements (Table2). So, I am trying
to concatenate 2 fields in DLookup, but it seems that I am not allowed to do
so:

For iRec = 1 To nRecordsAll
DoCmd.GoToRecord acDataTable, Table1, acGoTo, iRec

strSeek = "[Grp1] & [Item1]"
strReplace = "[Grp2new] & [Item2new]"
strNewGroup = DLookup("[Grp2new]", strSupers, _
"[Grp2old] & [Item2old] = " & strSeek)
strNewItem = DLookup("[Item2new]", strSupers, _
"[Grp2old] & [Item2old] = " & strSeek)
strSQL = "UPDATE " & strTempTable & " SET [Franchise] = " _
& strNewGroup & " WHERE .ID = " & Format(iRec)
CurrentDb().Execute strSQL, dbFailOnError
strSQL = "UPDATE " & strTempTable & " SET [Item] = " _
& strNewItem & " WHERE .ID = " & Format(iRec)
CurrentDb().Execute strSQL, dbFailOnError
Next iRec

The 1st call to DLookup generates:

"Run-time error 2001: You cancelled the previous operation"

so I am still going nowhere.

-- Al

* * *

 

[Duane Hookom]

I'm not sure what your requirements are here with this typ of code but if
strSeek is a text value, you need to delimit it in the DLookup()
strNewGroup = DLookup("[Grp2new]", strSupers, _
"[Grp2old] & [Item2old] = """ & strSeek & """")
All of this assumes strSupers is the name of a table or query.

--
Duane Hookom
MS Access MVP

Execution speed is nothing compared to the time this program has already
wasted. I only need to build the new table once, then use it as input to
another project.

I still need to change 2 fields at once, based on the values of 2 pairs of
corresponding fields in the index of replacements (Table2). So, I am
trying
to concatenate 2 fields in DLookup, but it seems that I am not allowed to
do
so:

For iRec = 1 To nRecordsAll
DoCmd.GoToRecord acDataTable, Table1, acGoTo, iRec

strSeek = "[Grp1] & [Item1]"
strReplace = "[Grp2new] & [Item2new]"
strNewGroup = DLookup("[Grp2new]", strSupers, _
"[Grp2old] & [Item2old] = " & strSeek)
strNewItem = DLookup("[Item2new]", strSupers, _
"[Grp2old] & [Item2old] = " & strSeek)
strSQL = "UPDATE " & strTempTable & " SET [Franchise] = " _
& strNewGroup & " WHERE .ID = " & Format(iRec)
CurrentDb().Execute strSQL, dbFailOnError
strSQL = "UPDATE " & strTempTable & " SET [Item] = " _
& strNewItem & " WHERE .ID = " & Format(iRec)
CurrentDb().Execute strSQL, dbFailOnError
Next iRec

The 1st call to DLookup generates:

"Run-time error 2001: You cancelled the previous operation"

so I am still going nowhere.

-- Al

* * *

This would run very slow with lots of records but might work. This
assumes
all fields are numeric. You would need to add a bunch of quotes of the
fields are text.

UPDATE Table1
SET Grp1 =
DLookup("Grp2new", "Table2","Grp2old =" & Grp1 & " AND Item2old = " &
Item1 );

 

[Dirk Goldgar]

(My previous reply didn't contain what I typed! Data lost due to
timeout?)

No, I can't see that working. I'm now trying to do it by stepping
through the records of Table1 in a VB loop. What syntax can I use to
change the value of a field in the current record?

What's wrong with

UPDATE
Table1 a
INNER JOIN
Table2 b
ON
a.Grp1 = b.Grp2old AND a.Item1 = b.Item2old
SET
a.Grp1 = b.Grp2new,
a.Item1 = b.Item2new;

?

 

[Allen_N]

What's wrong with

UPDATE
Table1 a
INNER JOIN
Table2 b
ON
a.Grp1 = b.Grp2old AND a.Item1 = b.Item2old
SET
a.Grp1 = b.Grp2new,
a.Item1 = b.Item2new;

?

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)

Nothing's wrong with it! (I just spent a good hour checking the results).
Thankyou very much.

I obviously need to get my head around this INNER JOIN thing, which Allen
Browne mentioned and I disregarded.

Thanks also to Allen and Duane!

 

[Allen_N]

Thanks for the info. I don't know how to program pumpkins or glass slippers,
though; I think your implication has eluded me.