[AccessD] Use a select in an update statement

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