|
database
newsgroups
|
|||||||||||||||||||||||
|
|||||||||||||||||||||||
UPDATE SET.... ORDER BY? Help!I then need to UPDATE this new column with values like: 7000,7001,7002, etc... I *ALMOST* have it here... but I need a way to sort this table on a certain field... so that the values (starting at 7000) are added in a certain order. DECLARE @t1 TABLE ( s1 VarChar(9), n1 SmallInt ) INSERT INTO @t1(s1) VALUES('One') INSERT INTO @t1(s1) VALUES('Two') INSERT INTO @t1(s1) VALUES('Three') INSERT INTO @t1(s1) VALUES('Four') DECLARE @t2 TABLE ( n2 SmallInt ) INSERT INTO @t2(n2) VALUES(1) INSERT INTO @t2(n2) VALUES(2) INSERT INTO @t2(n2) VALUES(3) INSERT INTO @t2(n2) VALUES(4) INSERT INTO @t2(n2) VALUES(5) DECLARE @i SmallInt ; SET @i=7000 UPDATE @t1 SET @i = n1 = @i + 1 -- ORDER BY s1 ??? SELECT * FROM @t1 How about doing it in a SELECT statement rather than an UPDATE? Otherwise
you'll have to change all the numbers whenever the data changes. SELECT A.s1, COUNT(*) AS n1 FROM @t1 AS A JOIN @t1 AS B ON A.s1 >= B.s1 GROUP BY A.s1 If you prefer the UPDATE: UPDATE T SET n1 = (SELECT COUNT(*) FROM @t1 WHERE s1 <= T.s1) FROM @t1 AS T SQL Server has no concept of an ordered UPDATE by the way. -- David Portas SQL Server MVP -- > How about doing it in a SELECT statement rather than an UPDATE? It would have to be an UPDATE. I'm trying to update each record... making a certain column contain the next numbering sequence. When done, that column needs to look like: 7000 7001 7002 7003 > Otherwise you'll have to change all the numbers whenever the data changes. It's for a 1-time "new column" that I've just added... and need to populate it with 7000,7001,7002, etc I thought I had found my solution (see my earlier post)... but it didn't work 100% when used with 1000s of records. I think the problem comes in when my subquery *IS* sorted as I need it... but then the UPDATE just modifies each record... in no particular order. So what *IS* the solution? Execute 1000s of individual UPDATE cmds... in a loop? Show quote "David Portas" <REMOVE_BEFORE_REPLYING_dpor***@acm.org> wrote in message news:qvadnaj1MbIKbh_fRVn-oA@giganews.com... > How about doing it in a SELECT statement rather than an UPDATE? Otherwise > you'll have to change all the numbers whenever the data changes. > > SELECT A.s1, COUNT(*) AS n1 > FROM @t1 AS A > JOIN @t1 AS B > ON A.s1 >= B.s1 > GROUP BY A.s1 > > If you prefer the UPDATE: > > UPDATE T > SET n1 = > (SELECT COUNT(*) > FROM @t1 > WHERE s1 <= T.s1) > FROM @t1 AS T > > SQL Server has no concept of an ordered UPDATE by the way. > > -- > David Portas > SQL Server MVP > -- > > If there is a "wrong order" for these new numbers, then
the order must mean something in your data model. Use the ORDER BY clause that puts the subquery results in the order you want to define the new column. update myTable set newColumn = ( 6999 + ( select count(*) from myTable as T where T.orderingColumn <= myTable.orderingColumn ) ) If you are trying to number the rows according to the order in which you see results from SELECT * FROM T, you are in trouble, since the order of those results is arbitrary. It isn't, for example, guaranteed the same as the order in which rows were inserted. But if that's the unfortunate case, you could try exporting the data to a text file (hoping it ends up in an order you can live with), applying a utility to number the rows of the text file, then reimporting with the new numbering column, which you can use to define the new column. It makes no sense to say things like "the order the rows are in the table". Rows in tables are not in any order. They can be retrieved in a specified order based on the data in the rows, using ORDER BY, or they can be retrieved in a meaningless order by a query without ORDER BY. Steve Kass Drew University Show quote "A_Michigan_User" wrote: >>How about doing it in a SELECT statement rather than an UPDATE? >> >> > >It would have to be an UPDATE. I'm trying to update each record... making a >certain >column contain the next numbering sequence. When done, that column needs >to look like: >7000 >7001 >7002 >7003 > > > >>Otherwise you'll have to change all the numbers whenever the data changes. >> >> > >It's for a 1-time "new column" that I've just added... and need to populate >it with 7000,7001,7002, etc > >I thought I had found my solution (see my earlier post)... but it didn't >work 100% when used with 1000s of records. > >I think the problem comes in when my subquery *IS* sorted as I need it... >but then the UPDATE just modifies >each record... in no particular order. > >So what *IS* the solution? >Execute 1000s of individual UPDATE cmds... in a loop? > > > > >"David Portas" <REMOVE_BEFORE_REPLYING_dpor***@acm.org> wrote in message >news:qvadnaj1MbIKbh_fRVn-oA@giganews.com... > > >>How about doing it in a SELECT statement rather than an UPDATE? Otherwise >>you'll have to change all the numbers whenever the data changes. >> >>SELECT A.s1, COUNT(*) AS n1 >>FROM @t1 AS A >>JOIN @t1 AS B >> ON A.s1 >= B.s1 >>GROUP BY A.s1 >> >>If you prefer the UPDATE: >> >>UPDATE T >>SET n1 = >> (SELECT COUNT(*) >> FROM @t1 >> WHERE s1 <= T.s1) >>FROM @t1 AS T >> >>SQL Server has no concept of an ordered UPDATE by the way. >> >>-- >>David Portas >>SQL Server MVP >>-- >> >> >> >> > > > > > If you are trying to number the rows according to the No. I'm trying to sort them by a specific ORDER BY clause.> order in which you see results from SELECT * FROM T, That's where the whole problem comes in... trying to UPDATE 1 column... with the records sorted in a specific order. > It makes no sense to say things like "the order the rows are in the I don't think I've ever said that here... or in my life.> table". I'll try your idea... and let you know in the tomorrow. Show quote "Steve Kass" <sk***@drew.edu> wrote in message news:eI96dKTbFHA.3912@TK2MSFTNGP15.phx.gbl... > If there is a "wrong order" for these new numbers, then > the order must mean something in your data model. Use > the ORDER BY clause that puts the subquery results in > the order you want to define the new column. > > update myTable set > newColumn = ( > 6999 + ( > select count(*) from myTable as T > where T.orderingColumn <= > myTable.orderingColumn > ) > ) > > If you are trying to number the rows according to the > order in which you see results from SELECT * FROM T, > you are in trouble, since the order of those results is arbitrary. > It isn't, for example, guaranteed the same as the order in which > rows were inserted. But if that's the unfortunate case, you could > try exporting the data to a text file (hoping it ends up in an order > you can live with), applying a utility to number the rows of the > text file, then reimporting with the new numbering column, which > you can use to define the new column. > > It makes no sense to say things like "the order the rows > are in the table". Rows in tables are not in any order. > They can be retrieved in a specified order based on the > data in the rows, using ORDER BY, or they can be retrieved > in a meaningless order by a query without ORDER BY. > > Steve Kass > Drew University > > "A_Michigan_User" wrote: > >>>How about doing it in a SELECT statement rather than an UPDATE? >>> >> >>It would have to be an UPDATE. I'm trying to update each record... making >>a certain >>column contain the next numbering sequence. When done, that column needs >>to look like: >>7000 >>7001 >>7002 >>7003 >> >> >>>Otherwise you'll have to change all the numbers whenever the data >>>changes. >>> >> >>It's for a 1-time "new column" that I've just added... and need to >>populate it with 7000,7001,7002, etc >> >>I thought I had found my solution (see my earlier post)... but it didn't >>work 100% when used with 1000s of records. >> >>I think the problem comes in when my subquery *IS* sorted as I need it... >>but then the UPDATE just modifies >>each record... in no particular order. >> >>So what *IS* the solution? >>Execute 1000s of individual UPDATE cmds... in a loop? >> >> >> >> >>"David Portas" <REMOVE_BEFORE_REPLYING_dpor***@acm.org> wrote in message >>news:qvadnaj1MbIKbh_fRVn-oA@giganews.com... >> >>>How about doing it in a SELECT statement rather than an UPDATE? Otherwise >>>you'll have to change all the numbers whenever the data changes. >>> >>>SELECT A.s1, COUNT(*) AS n1 >>>FROM @t1 AS A >>>JOIN @t1 AS B >>> ON A.s1 >= B.s1 >>>GROUP BY A.s1 >>> >>>If you prefer the UPDATE: >>> >>>UPDATE T >>>SET n1 = >>> (SELECT COUNT(*) >>> FROM @t1 >>> WHERE s1 <= T.s1) >>>FROM @t1 AS T >>> >>>SQL Server has no concept of an ordered UPDATE by the way. >>> >>>-- >>>David Portas >>>SQL Server MVP >>>-- >>> >>> >>> >> >> >> Sorry - I didn't mean to put words in your mouth. If you are using
an ORDER BY in the select query that works, then (assuming the ordering column is unique) the subquery with count(*) should work out. Let us know. SK Show quote "A_Michigan_User" wrote: >>If you are trying to number the rows according to the >>order in which you see results from SELECT * FROM T, >> >> > >No. I'm trying to sort them by a specific ORDER BY clause. > >That's where the whole problem comes in... trying to UPDATE 1 column... with >the records sorted in a specific order. > > > >>It makes no sense to say things like "the order the rows are in the >>table". >> >> > >I don't think I've ever said that here... or in my life. > >I'll try your idea... and let you know in the tomorrow. > >"Steve Kass" <sk***@drew.edu> wrote in message >news:eI96dKTbFHA.3912@TK2MSFTNGP15.phx.gbl... > > >>If there is a "wrong order" for these new numbers, then >>the order must mean something in your data model. Use >>the ORDER BY clause that puts the subquery results in >>the order you want to define the new column. >> >>update myTable set >> newColumn = ( >> 6999 + ( >> select count(*) from myTable as T >> where T.orderingColumn <= >> myTable.orderingColumn >> ) >>) >> >>If you are trying to number the rows according to the >>order in which you see results from SELECT * FROM T, >>you are in trouble, since the order of those results is arbitrary. >>It isn't, for example, guaranteed the same as the order in which >>rows were inserted. But if that's the unfortunate case, you could >>try exporting the data to a text file (hoping it ends up in an order >>you can live with), applying a utility to number the rows of the >>text file, then reimporting with the new numbering column, which >>you can use to define the new column. >> >>It makes no sense to say things like "the order the rows >>are in the table". Rows in tables are not in any order. >>They can be retrieved in a specified order based on the >>data in the rows, using ORDER BY, or they can be retrieved >>in a meaningless order by a query without ORDER BY. >> >>Steve Kass >>Drew University >> >>"A_Michigan_User" wrote: >> >> >> >>>>How about doing it in a SELECT statement rather than an UPDATE? >>>> >>>> >>>> >>>It would have to be an UPDATE. I'm trying to update each record... making >>>a certain >>>column contain the next numbering sequence. When done, that column needs >>>to look like: >>>7000 >>>7001 >>>7002 >>>7003 >>> >>> >>> >>> >>>>Otherwise you'll have to change all the numbers whenever the data >>>>changes. >>>> >>>> >>>> >>>It's for a 1-time "new column" that I've just added... and need to >>>populate it with 7000,7001,7002, etc >>> >>>I thought I had found my solution (see my earlier post)... but it didn't >>>work 100% when used with 1000s of records. >>> >>>I think the problem comes in when my subquery *IS* sorted as I need it... >>>but then the UPDATE just modifies >>>each record... in no particular order. >>> >>>So what *IS* the solution? >>>Execute 1000s of individual UPDATE cmds... in a loop? >>> >>> >>> >>> >>>"David Portas" <REMOVE_BEFORE_REPLYING_dpor***@acm.org> wrote in message >>>news:qvadnaj1MbIKbh_fRVn-oA@giganews.com... >>> >>> >>> >>>>How about doing it in a SELECT statement rather than an UPDATE? Otherwise >>>>you'll have to change all the numbers whenever the data changes. >>>> >>>>SELECT A.s1, COUNT(*) AS n1 >>>> >>>> >>>>FROM @t1 AS A >>> >>> >>>>JOIN @t1 AS B >>>> ON A.s1 >= B.s1 >>>>GROUP BY A.s1 >>>> >>>>If you prefer the UPDATE: >>>> >>>>UPDATE T >>>>SET n1 = >>>>(SELECT COUNT(*) >>>> FROM @t1 >>>> WHERE s1 <= T.s1) >>>> >>>> >>>>FROM @t1 AS T >>> >>> >>>>SQL Server has no concept of an ordered UPDATE by the way. >>>> >>>>-- >>>>David Portas >>>>SQL Server MVP >>>>-- >>>> >>>> >>>> >>>> >>>> >>> >>> >>> > > > >  |
|||||||||||||||||||||||
Other interesting topics