Page 1 of 2 12 Last
  • Jump to page:
    #1
  1. No Profile Picture
    Registered User
    ASP Explorer (0 - 99 posts)

    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
  3. Contributing User
    ASP Skiller (1500 - 1999 posts)

    Join Date
    Mar 2005
    Location
    Columbus, OH
    Posts
    1,581
    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
  4. #3
  5. No Profile Picture
    Registered User
    ASP Explorer (0 - 99 posts)

    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!
  6. #4
  7. Contributing User
    ASP Skiller (1500 - 1999 posts)

    Join Date
    Mar 2005
    Location
    Columbus, OH
    Posts
    1,581
    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
  8. #5
  9. No Profile Picture
    Registered User
    ASP Explorer (0 - 99 posts)

    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.
  10. #6
  11. Contributing User
    ASP Skiller (1500 - 1999 posts)

    Join Date
    Mar 2005
    Location
    Columbus, OH
    Posts
    1,581
    Rep Power
    278
    What version of SQL you are running?
  12. #7
  13. No Profile Picture
    Registered User
    ASP Explorer (0 - 99 posts)

    Join Date
    Oct 2012
    Posts
    9
    Rep Power
    0
    Microsoft Sql Server 2008 R2
  14. #8
  15. Contributing User
    ASP Skiller (1500 - 1999 posts)

    Join Date
    Mar 2005
    Location
    Columbus, OH
    Posts
    1,581
    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
  16. #9
  17. Contributing User
    ASP Skiller (1500 - 1999 posts)

    Join Date
    Mar 2005
    Location
    Columbus, OH
    Posts
    1,581
    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
  18. #10
  19. No Profile Picture
    Registered User
    ASP Explorer (0 - 99 posts)

    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'.
  20. #11
  21. No Profile Picture
    Registered User
    ASP Explorer (0 - 99 posts)

    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
  22. #12
  23. No Profile Picture
    Registered User
    ASP Explorer (0 - 99 posts)

    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
  24. #13
  25. No Profile Picture
    Registered User
    ASP Explorer (0 - 99 posts)

    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!!
  26. #14
  27. Contributing User
    ASP Skiller (1500 - 1999 posts)

    Join Date
    Mar 2005
    Location
    Columbus, OH
    Posts
    1,581
    Rep Power
    278
    as long as it is not a millions records in a table you will not see any performance issues...
  28. #15
  29. No Profile Picture
    Registered User
    ASP Explorer (0 - 99 posts)

    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 Last
  • Jump to page:

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

IMN logo majestic logo threadwatch logo seochat tools logo