[dba-SQLServer] Use a select in an update statement

Hi John,

Probably better ways to do it but what comes to mind first is using a
temp table.

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

Join the temp table back into your target table [tblOrderData] and do
the Update.
Obviously you need a key column to join back on, if its not KeyCode then
you will need to adjust to suit.

	UPDATE tblOrderData
	SET KeyCode = 'KeyA'
	FROM tblOrderData INNER JOIN #Temp ON tblOrderData.KeyCode =
#Temp.KeyCode

To make it Dynamic you end up with something like...


SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE dbo.DynamicUpdateKeyCode
	@top INT,
	@NewKeyCode nvarchar(50)
AS
BEGIN

	SET NOCOUNT ON;

	DECLARE @sql nvarchar(4000),
			@params nvarchar(4000)

SELECT @sql = '
	Select top @xtop KeyCode
	INTO #Temp
	 From tblOrderData
	Where NoChildren = ''2''
	Order By RandomNumber
	'

PRINT @sql

	SELECT @params = 
		'@xtop INT'

	EXEC sp_executesql @sql , @params, @top
	--Join the temp table back into your target table [tblOrderData]
and do the Update.

SELECT @sql = '	
	UPDATE tblOrderData
	SET KeyCode = ''@xNewKeyCode''
	FROM tblOrderData INNER JOIN #Temp ON tblOrderData.KeyCode =
#Temp.KeyCode'

PRINT @sql

	SELECT @params = 
		'@xNewKeyCode nvarchar(50)'

	EXEC sp_executesql @sql , @params, @NewKeyCode
	
	DROP TABLE #Temp;


END
GO

HTH

Michael M



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
_______________________________________________
dba-SQLServer mailing list
dba-SQLServer at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/dba-sqlserver
http://www.databaseadvisors.com