UPDATE (SQL): multiple SET fields not allowed?

[Guest]

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.

 

[Guest]

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.

 

[Guest]

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

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

 

[Guest]

(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 );

 

[Guest]

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;

?

 

[Guest]

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!

 

[onedaywhen]

Does Access simply not allow multi-field
UPDATE queries?

Just to clarify, Jet does not support the SQL-92 standard syntax for
UPDATE e.g.

UPDATE <table>
SET <col> = (<scalar subquery>)

In turn, Jet's own proprietary UPDATE syntax violates the SQL-92
standard. To be fair, Jet's syntax predates the standards.

They got closer with the Jet 4.0 release but the UPDATE syntax is a
good example about how Jet gathers dust in the cellar.

Looks you may have to learn Cinderella's way of doing things <g>.

Jamie.

--

 

[Guest]

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

 

[onedaywhen]

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

I cast Jet as Cinderella, simple and graceful but no one can see her
beauty (e.g. the DECIMAL data type) because her ugly sisters (played by
Allen Browne) keep her hidden away (with fallacies of 'wildly
inaccurate' and 'unpredictable' sorting). Has her Prince Charming (the
Access2007 team taking a 'private' copy of Jet) arrived to shower her
with riches (entry level SQL-92 compliance? fix the DECIMAL bad sort
bug?) or will he force her to continue to earn her keep (who had
'complex data' and 'compatibility with SharePoint' on their wish list,
eh?!)

Perhaps I should have said "poor man's SQL database" instead. There is
no money to fix existing bugs/features in Jet.

Jamie.

--