[AccessD] Use a select in an update statement

jwcolby jwcolby at colbyconsulting.com
Wed Aug 5 17:03:59 CDT 2009


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



More information about the AccessD mailing list