Update while sorting

[MiRo]

Hi everyone
I'm trying to change my table from a alphabetical sort to a numeric one. So I've added the field prod_sort2 where I want to have whole consecutive numbers (meaning 1 2 3 and no commas or dots).
What I've been trying is the following:

Declare @intFlag INT
SET @intFlag= 1
while (@intFlag <7424) --7424 is the amount of records I have in the table
Begin
Print @intFlag
update prodlist set  prod_sort2=@intFlag where auto_id in (select top 7424 * from prodlist order by prod_cat,prod_subcat,prod_sort)
Set @intFlag = @intFlag +1
End
Go

It gives me the following error: Only one expression can be specified in the select list when the subquery is not introduced with EXISTS.
I dont mind if you give me a different idea how to do it. I tried a few things and I didnt manage to get to work any of them.
Your help is greatly appreciated!
MiRo

[gk53]

The problem is
where auto_id in (select top 7424 * from prodlist order by prod_cat,prod_subcat,prod_sort)
it must be
where auto_id in (select top 7424 auto_id from prodlist order by prod_cat,prod_subcat,prod_sort)
in sub select you must have only one field...

but it is much better to use row_number or identity(1,1) for that
if you publicsh our table structure I can help you with SQL

[MiRo]

do you mean this?

CREATE TABLE [User_D61D7B].[ProdLIST](
	[Auto_ID] [int] NOT NULL,
	[Prod_SORT] [nvarchar](25) NULL,
	[Prod_CODE] [nvarchar](25) NOT NULL,
	[Prod_SKU] [nvarchar](100) NULL,
	[Prod_NAME] [nvarchar](250) NOT NULL,
	[Prod_CAT] [smallint] NOT NULL,
	[Prod_subCAT] [int] NOT NULL,
	[Prod_Description] [ntext] NULL,
	[Prod_Price1] [money] NOT NULL,
	[Prod_Price2] [money] NOT NULL,
	[Prod_Style] [int] NULL,
	[Prod_Finish] [int] NULL,
	[Prod_Usage] [int] NULL,
	[Prod_Type] [int] NULL,
	[Prod_PriceRange] [int] NULL,
	[Prod_Size] [int] NULL,
	[Special1] [smallint] NOT NULL,
	[Special2] [smallint] NOT NULL,
	[Prod_Stock] [tinyint] NOT NULL,
	[Prod_Date] [smalldatetime] NULL,
	[Prod_Collection] [nvarchar](250) NULL,
	[Prod_Collection_SORT] [nvarchar](250) NULL,
	[Prod_XLPic] [tinyint] NOT NULL,
	[Prod_Active] [tinyint] NOT NULL,
	[Prod_ManufactureNum] [varchar](100) NULL,
	[Prod_Price3] [money] NULL,
	[Prod_Brand] [nvarchar](100) NULL,
	[Prod_Sort2] [int] NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
I made it a bit more efficient by writing:

	
	
SET @rowCount = (SELECT COUNT (*) FROM ProdList)
Thank you for your help!

[gk53]

Try that. I hope [Auto_ID] is unique key for the table if not you need to change join condition...

;with T as
(
select [Auto_ID],
	[Prod_SORT],
	[Prod_CAT] ,
	[Prod_subCAT],
	[Prod_Sort2],
	ROW_NUMBER()over(order by prod_cat,prod_subcat,prod_sort) as rNUm
	from ProdLIST
--	order by prod_cat,prod_subcat,prod_sort
)
update P
set P.Prod_Sort2 = T.rNUm
from ProdLIST p
join T
on P.Auto_ID=T.Auto_ID

[MiRo]

I get the errors:
Msg 170, Level 15, State 1, Line 1
Line 1: Incorrect syntax near ';'.
Msg 195, Level 15, State 10, Line 8
'ROW_NUMBER' is not a recognized function name.

[gk53]

What version of SQL you are running?

[MiRo]

Microsoft Sql Server 2008 R2

[gk53]

I tried that on SQL2008 R2 and it is works. If you have only one statement in your window you can remove first ";"
ROW_NUMBER() is standard function for SQL 2008

[gk53]

this is another solution for your problem

declare @ProdLIST
as table
(
	[Auto_ID] [int] NOT NULL,
	[Prod_SORT] [nvarchar](25) NULL,
	[Prod_CODE] [nvarchar](25) NOT NULL,
	[Prod_SKU] [nvarchar](100) NULL,
	[Prod_NAME] [nvarchar](250) NOT NULL,
	[Prod_CAT] [smallint] NOT NULL,
	[Prod_subCAT] [int] NOT NULL,
	[Prod_Description] [ntext] NULL,
	[Prod_Price1] [money] NOT NULL,
	[Prod_Price2] [money] NOT NULL,
	[Prod_Style] [int] NULL,
	[Prod_Finish] [int] NULL,
	[Prod_Usage] [int] NULL,
	[Prod_Type] [int] NULL,
	[Prod_PriceRange] [int] NULL,
	[Prod_Size] [int] NULL,
	[Special1] [smallint] NOT NULL,
	[Special2] [smallint] NOT NULL,
	[Prod_Stock] [tinyint] NOT NULL,
	[Prod_Date] [smalldatetime] NULL,
	[Prod_Collection] [nvarchar](250) NULL,
	[Prod_Collection_SORT] [nvarchar](250) NULL,
	[Prod_XLPic] [tinyint] NOT NULL,
	[Prod_Active] [tinyint] NOT NULL,
	[Prod_ManufactureNum] [varchar](100) NULL,
	[Prod_Price3] [money] NULL,
	[Prod_Brand] [nvarchar](100) NULL,
	[Prod_Sort2] [int] NULL,
	rowNum int null
)

select *, IDENTITY(int, 1,1) as rowNum
into @ProdLIST 
from [User_D61D7B].[ProdLIST]

update p
set p.[Prod_Sort2]=rowNum
from [User_D61D7B].[ProdLIST] p	
inner join 	@ProdLIST t
on P.[Auto_ID]=t.Auto_ID

[MiRo]

Thanks. Now I'm getting:
Msg 156, Level 15, State 1, Line 2
Incorrect syntax near the keyword 'table'.
Msg 170, Level 15, State 1, Line 36
Line 36: Incorrect syntax near '@ProdLIST'.
Msg 137, Level 15, State 2, Line 42
Must declare the variable '@ProdLIST'.

[MiRo]

Hi GK
Thank you so much. I still got one error message : Line 33: Incorrect syntax near '@ProdLIST'.
(by the select * ,rownum into @prodlist.
I think it's because you can't do that to a table variable - Using TABLE Variables - SQLTeam.com
Thanks again for your help!
MiRo

[MiRo]

I tried it now with a real table:

SELECT	auto_id, prod_cat,prod_subcat,prod_sort, ID=IDENTITY (int, 1, 1)
INTO 	NewTable
FROM 	prodlist
Order By prod_cat,prod_subcat,prod_sort

I'll try now to copy the value stored in ID into prodlist.prod_sort2

[MiRo]

I used your update statement. I know with the real table it may be wasn't so efficient, but it WORKED!!
Thanks so much for all your patience and help!!

[gk53]

as long as it is not a millions records in a table you will not see any performance issues...

[MiRo]

It was only about 4,5 thousand. I deleted the table straight after that.
Can I ask you a different question:
Momentarily the way it works that each product is under one category and subcategory (connected thru a number in 2 different tables - one called "cat" and one "cat_sub" respectively).
Now my boss wants to be able to put one product under more than one category and subcategory. I know that one can do it with a new table (id, prod_code, cat, subcat) but then I'd have to change all the code around to make it work again (f.ex. when I have drop down, it would have to loop thru more tables).
Do you have any solution besides for a separate table?
Thanks!