Notices

Great community. Great ideas.

Welcome to ASP/Free, a community dedicated to helping beginners and professionals alike in improving their knowledge of Microsoft's development and administration technologies. Sign up today to gain access to the combined insight of tens of thousands of members.

Thread: Update while sorting

Page 1 of 2 12 LastLast
Results 1 to 15 of 16
Share This Thread →
  1. #1
    MiRo is offline Registered User
    Join Date
    Oct 2012
    Posts
    9
    Rep Power
    0

    Update while sorting

    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:
    Code:
    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

  2. #2
    gk53's Avatar
    gk53 is offline Contributing User
    Join Date
    Mar 2005
    Location
    Columbus, OH
    Posts
    1,538
    Rep Power
    278
    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
    GK
    __________________________________________________ _____
    if you found this post is useful click Give Rep button (bottom side on this reply ) and agree

  3. #3
    MiRo is offline Registered User
    Join Date
    Oct 2012
    Posts
    9
    Rep Power
    0
    do you mean this?

    Code:
    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:
    
    Code:
    SET @rowCount = (SELECT COUNT (*) FROM ProdList)
    Thank you for your help!

  4. #4
    gk53's Avatar
    gk53 is offline Contributing User
    Join Date
    Mar 2005
    Location
    Columbus, OH
    Posts
    1,538
    Rep Power
    278
    Try that. I hope [Auto_ID] is unique key for the table if not you need to change join condition...

    Code:
    ;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

  5. #5
    MiRo is offline Registered User
    Join Date
    Oct 2012
    Posts
    9
    Rep Power
    0
    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.

  6. #6
    gk53's Avatar
    gk53 is offline Contributing User
    Join Date
    Mar 2005
    Location
    Columbus, OH
    Posts
    1,538
    Rep Power
    278
    What version of SQL you are running?

  7. #7
    MiRo is offline Registered User
    Join Date
    Oct 2012
    Posts
    9
    Rep Power
    0
    Microsoft Sql Server 2008 R2

  8. #8
    gk53's Avatar
    gk53 is offline Contributing User
    Join Date
    Mar 2005
    Location
    Columbus, OH
    Posts
    1,538
    Rep Power
    278
    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

  9. #9
    gk53's Avatar
    gk53 is offline Contributing User
    Join Date
    Mar 2005
    Location
    Columbus, OH
    Posts
    1,538
    Rep Power
    278
    this is another solution for your problem

    Code:
    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

  10. #10
    MiRo is offline Registered User
    Join Date
    Oct 2012
    Posts
    9
    Rep Power
    0
    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'.

  11. #11
    MiRo is offline Registered User
    Join Date
    Oct 2012
    Posts
    9
    Rep Power
    0
    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

  12. #12
    MiRo is offline Registered User
    Join Date
    Oct 2012
    Posts
    9
    Rep Power
    0
    I tried it now with a real table:
    Code:
    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

  13. #13
    MiRo is offline Registered User
    Join Date
    Oct 2012
    Posts
    9
    Rep Power
    0
    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!!

  14. #14
    gk53's Avatar
    gk53 is offline Contributing User
    Join Date
    Mar 2005
    Location
    Columbus, OH
    Posts
    1,538
    Rep Power
    278
    as long as it is not a millions records in a table you will not see any performance issues...

  15. #15
    MiRo is offline Registered User
    Join Date
    Oct 2012
    Posts
    9
    Rep Power
    0
    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!

Page 1 of 2 12 LastLast
Share This Thread →

Become Part of This Conversation

Join NowFor Free!

Similar Threads

  1. Replies: 14
    Last Post: September 28th, 2012, 08:30 AM
  2. ASP Sorting Code
    By LearningDevelop in forum ASP Development
    Replies: 2
    Last Post: September 27th, 2012, 12:47 AM
  3. Update query
    By meera89 in forum Microsoft SQL Server
    Replies: 1
    Last Post: July 13th, 2012, 04:31 AM
  4. Update page without refresh
    By robert475 in forum ASP Development
    Replies: 1
    Last Post: March 14th, 2011, 07:55 AM
  5. Update query
    By jonyBravo in forum Microsoft SQL Server
    Replies: 6
    Last Post: September 13th, 2008, 02:05 PM

ASP Free Advertisers and Affiliates