[dba-SQLServer] You'll love this!

Gustav,

I solved it using common table expressions (CTE), which I am growing to love.

In my tblOrderData I have a set of fields which I use to hold codes for specific selections.  In a 
previous stored procedure I had set the sequence field of two sets of records to either a 22 or an 11.

Using a CTE I created a tLargeZip common table where I pulled all of the Zip5 for records with 
Sequence = '22', grouped by Zip5 where the count(PK) >= 20.

I created another CTE OrderData which joined back to TLargeZips on Zip5, and then updated the 
Selection to a code '22GTE20' - meaning Sequence 22 with Zip count GTE 20.

		UPDATE [tblOrderData]
		SET [Selection] = NULL;

		With
		
		TLargeZips (Zip5) as (
			SELECT     TOP (100) PERCENT zip5
			FROM         dbo.vOrder
			WHERE     (Sequence = '22')
			GROUP BY zip5
			HAVING      (COUNT(PK) >= 20)
		),
		
		OrderData (Selection) as (
		SELECT Selection from dbo.vOrder
			INNER JOIN TLargeZips ON dbo.vOrder.Zip5 = TLargeZips.zip5
			
			WHERE (Selection IS NULL) AND (Sequence = '22')
		)
		
		UPDATE OrderData
		SET Selection = '22GTE20';

I am using CTEs more and more as it allows me to create logical data sets which can then be 
manipulated in another sequel statement.

Essentially the CTE replaces the temp table that you suggested with an "in memory" table, created 
on-the-fly.


John W. Colby
www.ColbyConsulting.com

On 9/22/2010 2:23 AM, Gustav Brock wrote:
> Hi John
>
> Why not write the list of those 20+ zip items to a temp table, then join this with your table to update?
>
> /gustav
>
>
>>>> jwcolby at colbyconsulting.com 22-09-2010 06:28>>>
> My client asked me to sum (give him a count of) the records in zips where the count in each zip is>  20.
>
> No problem, do a count group by zip, where the count>  20.  This gives me a list of such zip codes.
>    Then build a sum on that base query.
>
> No comes the not so cool part.  I will have to select my records only from zips where the count of
> records in a zip is>=20.
>
> This may be easy, join the base query back to the table and update a field to a code, then select
> from the table where that code is set.
>
> My fear is that the join will make the view non-updateable.  Which will leave me using a cursor?
>
> We shall see.
>