[AccessD] Use a select in an update statement

Jet SQL or SQL Server?

-- 
Stuart

On 5 Aug 2009 at 18:03, jwcolby wrote:

> 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
> -- 
> AccessD mailing list
> AccessD at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/accessd
> Website: http://www.databaseadvisors.com