[AccessD] Use a select in an update statement

That is exactly what I want to do but I have to learn how to use the select statement inside of the 
update statement.  THAT is the question.

I know how to construct the SP, pass in parameters and create and execute dynamic SQL.

Given

Select top 10000 KeyCode
 From tblOrderData
Where NoChildren = '2'
OrderBy RandomNumber

Stored in view vSelKeyCode

And then the update statement

UPDATE vSelKeyCode
SET KeyCode = 'KeyA'

replace vSelKeyCode in the update statement with the equivalent SQL above.

I tried it and get an error.

John W. Colby
www.ColbyConsulting.com


Charlotte Foust wrote:
> If it's a stored procedure, why not make it accept input parameters and
> then just pass them in ... Or am I misunderstanding what you want to do?
> 
> Charlotte Foust
> 
> -----Original Message-----
> From: accessd-bounces at databaseadvisors.com
> [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of jwcolby
> Sent: Wednesday, August 05, 2009 3:04 PM
> To: Access Developers discussion and problem solving; Dba-Sqlserver
> Subject: [AccessD] Use a select in an update statement
> 
> I need to update a field based on the top N values in another field kind
> of thing.
> 
> IOW
> 
> Select top 10000 KeyCode
>  From tblOrderData
> Where NoChildren = '2'
> OrderBy RandomNumber
> 
> Now, I need to UPDATE Keycode to 'KeyA'
> 
> I can save the Select statement to vSelKeyCode and then do an update on
> that as follows:
> 
> UPDATE vSelKeyCode
> SET KeyCode = 'KeyA'
> 
> I then need to do this many times, changing the TOP() value and
> NoOfChildren code.
> 
> It seems logical that I could replace vSelKeyCode with the Select
> statement contained in vSelKeyCode but I get errors when I attempt this.
> 
> Is it possible to do this?
> 
> What I am trying to do in the end is create a stored procedure that
> allows me to pass in the TOP() value, NoOfChildren code and Key value
> and build dynamic code to perform this update.
> 
> In the past I have just created N stored views with the correct TOP()
> value and NoOfChildren code hard coded, then performed an Update to each
> of those stored views.  While that works it is a PITA and subject to all
> kinds of errors.
> 
> There has to be a better way, and I don't know what it is.  The dynamic
> SQL statement in a stored procedure is my answer, and lead me to the
> damned gallows!
> 
> --
> John W. Colby
> www.ColbyConsulting.com
>