UPDATE with a SELECT - is it possible?

[Jesper F]

Is it possible to update a table with something like this:

UPDATE table1
(firstname,lastname)
SELECT 'newfirstname' as f, 'newlastname' as l
WHERE table1.personID = 5

or must one use something like:
UPDATE table1 SET field1 = 'xx', field2='yy' WHERE personID=5

I mean, can one update a range in a table with a range selected somewhere
else?



Jesper

 

[Gary Walter]

Is it possible to update a table with something like this:

UPDATE table1
(firstname,lastname)
SELECT 'newfirstname' as f, 'newlastname' as l
WHERE table1.personID = 5

or must one use something like:
UPDATE table1 SET field1 = 'xx', field2='yy' WHERE personID=5

I mean, can one update a range in a table with a range selected somewhere
else?

Hi Jesper,

Technically, your first query will fail
and you would need to use something
like second query.

As for "range-to-range,"

I believe following is quote from Michel:

***quote***

Access SQL DOES NOT support subqueries in SET clause
(ANSI SQL does)

Access SQL DOES support joins in UPDATE clause
(nonstandard SQL)

***unquote***

some simple examples:

UPDATE tbl1 INNER JOIN tbl2
ON tbl1.PK = tbl2.PK
SET
tbl1.f1 = tbl2.f1,
tbl1.f2 = tbl2.f2

UPDATE tbl1 INNER JOIN tbl2
ON tbl1.PK = tbl2.PK
SET
tbl1.f1 = tbl2.f1
WHERE tbl1.f1<>tbl2.f1;

you can "hide" subqueries in WHERE clause:

'update query to flag all records
'where FileNames are same
'but different Version or FileSize

UPDATE t1
SET t1.DiffVerSize = '*****'"
WHERE
(t1.filename) In
(SELECT Tmp.FileName
FROM t1 AS Tmp
GROUP BY Tmp.FileName
HAVING
(((Count(*))>1) AND ((Max(Tmp.Version))<>Min([Version])))
OR
(((Count(*))>1) AND ((Max(Tmp.FileSize))<>Min([FileSize]))));


The 2 original example queries join on the primary keys
so we can assume only one record in tbl1 will match up
with only one record in tbl2. That is the typical case
for "range-to-range" update queries.

An even more interesting example of update queries
is using arithmetic to produce aggregation as once
illustrated again by Michel:

***quote****

for a SUM:

UPDATE summary INNER JOIN baseTable
ON summary.group = baseTable.group
SET summary.SumOfAmount =
summary.SumOfAmount + baseTable.Amount

or for a LAST:

UPDATE summary INNER JOIN baseTable
ON summary.group = baseTable.group
SET summary.LastOfAmount = baseTable.Amount

or for a MAX:

UPDATE summary INNER JOIN baseTable
ON summary.group = baseTable.group
SET summary.MaxOfAmount =
IIF(baseTable.Amount> summary.MaxOfAmount,
baseTable.Amount, summary.MaxOfAmount)


That assumes that the summary fields are initialized properly before
starting the updates.

***unquote***

by joining on non-pk fields (a "group field", or could be "group fields"),
a specific summary record may be joined to *one/several/or many*
records in the base table.

In the case of the SUM example, we would start with
setting all summary.SumOfAmount to 0.

For every record in summary table, *all* the records in
basetable are "cycled through" where they match on
the "group" fields, resulting in an aggregate sum.

I'm not saying this is the best way to do this, but
just wanted to illustrate the effect of JOIN
in a "range-to-range" update query if you do
not join on the primary keys.

If you do not join your ranges on pk's
(or join fields that produce only one range record
per only one record in the other range),
then you will be doing the equivalent of
Michel's LAST example.

good luck,

gary

 

[Jesper F]

by joining on non-pk fields (a "group field", or could be "group fields"),

a specific summary record may be joined to *one/several/or many*
records in the base table.

In the case of the SUM example, we would start with
setting all summary.SumOfAmount to 0.

For every record in summary table, *all* the records in
basetable are "cycled through" where they match on
the "group" fields, resulting in an aggregate sum.

I'm not saying this is the best way to do this, but
just wanted to illustrate the effect of JOIN
in a "range-to-range" update query if you do
not join on the primary keys.

If you do not join your ranges on pk's
(or join fields that produce only one range record
per only one record in the other range),
then you will be doing the equivalent of
Michel's LAST example.

Hi Gary, Thank you very much for that very thorough reply.
I think I can use the joins in my situation. Great stuff.

Jesper

 

[Gary Walter]

Is it possible to update a table with something like this:

UPDATE table1
(firstname,lastname)
SELECT 'newfirstname' as f, 'newlastname' as l
WHERE table1.personID = 5

or must one use something like:
UPDATE table1 SET field1 = 'xx', field2='yy' WHERE personID=5

I mean, can one update a range in a table with a range selected somewhere
else?



Jesper

Hi Jesper,

You might not read this, so I am reposting here
the following recent reply by Michel to another post:

***quote***

You cannot SELECT a list of constants, as a sub-query.

You may try

(SELECT cte1, cte2 FROM tableName WHERE somethingTrueFormJustOneRecord)


or

(SELECT DISTINCT cte1, cte2 FROM tableName)


but try to supply a FROM clause.

In the immediate debug window, you can try:


? CurrentProject.Connection.Execute("SELECT 5").Fields(0).Value
5

? CurrentProject.Connection.Execute( "SELECT (SELECT 5 AS y) AS x
" ).Fields(0).Value
---error




Hoping it may help,
Vanderghast, Access MVP

***unquote***

The "update/subquery in Access" limitation
still applies, but something else to think about
when going from SQL Server to Access which,
by the nature of your question, I suspect might
be your case.

good luck,

gary