Re: UPDATE with a SELECT - is it possible?


"Jesper F" wrote:
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



.