InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comWed, 22 May 2013 20:05:46 GMT20http://www.sqlservercentral.com/Forums/Topic546486-1226-1.aspxNice article. I liked very much seeing the "temp-in-place" technique applied, I guess it's very rare these days.But I didn't recognise the description of the early 70s. I started in computing somewhere between 5 and 10 years ealier than you, and although I did work briefly on (sometimes "in" rather than "on" - walking around inside them was not uncommon) machines with very small stores (one using mercury delay lines for main storage, another using magnetostrictors) , they were already very obsolescent and long before 1970 this sort of thing was thoroughly obsolete. Typical "small" machines of the early 60s like the Elliott 803 (1959) and the IBM 1620 (also 1959) and the PDP-1 (1960?) had minimum shipable configurations with thousands of words/digits/whatever (they didn't use "bytes") of memory - you couldn't get one http://www.sqlservercentral.com/Forums/Skins/Classic/Images/RichTextBoxTable/tbl_bottomleft.gifwith less than 160kbits, 60kbits(?? maybe 72kbits, can't remember), and 72kbits respectively. And of course the at the opposite extreme were were things like the IBM 709, with 1152kbits memory, as early as 1958, the Ferranti Atlas I with 5Mbits of "One level storage" (Virtual Memory) 1962 and Ferranti Titan with 6Mbits of core store in 1964. I can't remember even seeing a machine with as little main memory as you mention after March 1969 (except in museums).As for kilobyte discs, I was using 7Mbyte removable discs in 1967 (7Mbytes capacity, not kilobytes). These were the cheap option - the expensive option was 30 MBytes on a single exchangeable disc pack (the 30MByte version - actually 29MByte - was introduced by IBM early in 1965). Even some "drums" (in quotes because they were mostly single platter discs with a head per track, instead of the cylindrical drums with recording surface parallel to the axis of rotation of the 1950s) were by then up to 1 Mbyte. I really don't remember discs measured in kilobytes.So it would be nice to know what machine models you were using - there seems to be a whole thread in the development of modern computers that I somehow managed to miss, and I'd like to read up on it.Thu, 12 Nov 2009 13:37:16 GMTL' Eomot Inverséhttp://www.sqlservercentral.com/Forums/Topic546486-1226-1.aspx[quote][b]JLSSCH (11/2/2009)[/b][hr]One final update to my solution. Using CTE's, the output query is much easier to understand.[/quote]CTE's also are not avaialble in SQL Server 2000.Tue, 03 Nov 2009 21:54:02 GMTRBarryYounghttp://www.sqlservercentral.com/Forums/Topic546486-1226-1.aspx[quote][b]JLSSCH (11/2/2009)[/b][hr]Thanks for your comments. The following modified solution works when you use the row_number() function to number the rows in an output query.[/quote]ROW_NUMBER() makes it easy. However, let's review the restrictions:[quote]To this the OP adds the following restrictions:No intermediate tables, andNo additional identity column.And implicit to this, of course, is that [i]it is for SQL2000[/i].[/quote]The ROW_NUMBER() function is SQL Server 2005.Tue, 03 Nov 2009 21:52:49 GMTRBarryYounghttp://www.sqlservercentral.com/Forums/Topic546486-1226-1.aspx[quote][b]Wanderlei Santos (10/30/2009)[/b][hr]I know this is an old article, and maybe someone already offered this solution, but to be honest the comment list is just too long for me to read it all...So I used your approach, but instead of the sex column, I used the age, which I assumed was an integer. ...[/quote]Oh, I definitely considered using the Age column, and it sure would have been easier if it was indeed an INTEGER. The problem is though, that we [i]cannot[/i] assume that it is an INTEGER, since a TINYINT is more than sufficient. Since a TINYINT saves three bytes per row in the table over an INTEGER, there is every possibility that it is not in fact a full INTEGER.Mon, 02 Nov 2009 18:52:07 GMTRBarryYounghttp://www.sqlservercentral.com/Forums/Topic546486-1226-1.aspx[quote][b]BlackHawk-17 (10/30/2009)[/b][hr]What a great thought experiment! :smooooth:It was fun to watch the problem solving unfold and the answer develop.These are the articles that keep me coming back.Thanks a ton for sharing;Greg[/quote]Thanks Greg, that's great to hear. :-)Mon, 02 Nov 2009 18:46:09 GMTRBarryYounghttp://www.sqlservercentral.com/Forums/Topic546486-1226-1.aspx[quote][b]Tom Garth (10/30/2009)[/b][hr]Thanks for the thought provoking article R, and 5 stars for a good read!I've used the [i][b]Temp In-Place [/b][/i] method in the past. Sometimes to fix my own mistakes. I never knew it had a name.An aside: Assuming tinyint for the age would be incorrect, since the primary solution in the article drove the column negative.[/quote]Tinyint cannot go negative, so it gets to use positive integers up to 255. (try the following code to demonstrate):[code]declare @t tinyint;set @t = 127select @t = @t + 2select @t[/code]Mon, 02 Nov 2009 18:45:20 GMTRBarryYounghttp://www.sqlservercentral.com/Forums/Topic546486-1226-1.aspx[quote][b]sjimmo (10/30/2009)[/b][hr]Brings back many fond memories, or nightmares back then. Excellent article, which has the opportunity to not only show alternatives to how we do things now, but also provides opportunities to expand our knowledgebase with alternative examples. Never know when one may run into some of these opportunities.[/quote]Thanks, sjimmo!Mon, 02 Nov 2009 18:42:14 GMTRBarryYounghttp://www.sqlservercentral.com/Forums/Topic546486-1226-1.aspx[quote][b]arms.dan (10/30/2009)[/b][hr]Your off-hand comment about naming the field "Gender" rather than "Sex" piqued my curiosity. Why? As I understand the words, sex means biological differences: chromosomes and sexual organs. Gender refers to the characteristics a society or culture delineates as masculine or feminine. There's some blurring of the definitions, but I think they generally hold. So why do you prefer Gender for the column label?[/quote]In English "Sex" can also be a verb, thus leading to the joke about filling forms where is says "Sex[_]" with "Y". The PC way to stop this is to change the field name to "Gender[_]".Mon, 02 Nov 2009 18:40:34 GMTRBarryYounghttp://www.sqlservercentral.com/Forums/Topic546486-1226-1.aspx[quote][b]jghali (10/30/2009)[/b][hr]Funny how this article just came out today... I was actually on a similar thread last week and someone came up with a great solution... My personal opinion the article is interesting but ... hmmm...How about these solutions from this thread... http://www.sqlservercentral.com/Forums/Topic793765-145-1.aspxThere's an undocumented identity key for every row of any table that can be used... in one simple delete statement you can delete the duplicate rows...I was amazed... Check it out.Thanks[/quote]I am familiar with that thread (in fact I think that I am posted there), however, 1) it came out long after this article was originally published (15 months ago) and 2) those internal IDs are a lot hard to use than you might think, esp. given the restrictions. For instance, how would you propose using them to solve this problem?Mon, 02 Nov 2009 18:35:01 GMTRBarryYounghttp://www.sqlservercentral.com/Forums/Topic546486-1226-1.aspx[quote][b]skjoldtc (10/30/2009)[/b][hr]Thanks for the memories. A time when storage and memory was rare and expensive and you had to really know how the guts of the operating system, disk system, and memory allocation worked. Some of the old techniques are still useful today.Great article and a great solution. :cool:[/quote]Thanks!Mon, 02 Nov 2009 18:30:47 GMTRBarryYounghttp://www.sqlservercentral.com/Forums/Topic546486-1226-1.aspx[quote][b]Stephen.Richardson (10/30/2009)[/b][hr]...-- Step 1 Get list of only DUPLICATE rowsINSERT INTO xSource (Name, Age, Sex) SELECT '~'+Name, Age, Sex FROM xSource GROUP BY Name, Age, Sex Having COUNT(*) > 1[/quote]The problem is that this will fail if someone's name is already using all 50 characters (not unheard of). Otherwise I would have used this approach myself. :-)Mon, 02 Nov 2009 18:29:52 GMTRBarryYounghttp://www.sqlservercentral.com/Forums/Topic546486-1226-1.aspx[quote][b]honza.mf (10/30/2009)[/b][hr][quote][b]Phil Factor (8/5/2008)[/b][hr]Surely this is the simplest solution? (be warned of a nasty catch when creating these 'quirky updates'. the order of execution of the update is -variable assignments first then column assignments-both left to right!)[/quote]Very nice solution.I Just prefer little change, it's more readable[font="Courier New"]--create the sample tableDECLARE @Sample TABLE (TheName VARCHAR(4),Identifier INT, sex VARCHAR(1))INSERT INTO @Sample (TheName,Identifier,Sex) SELECT 'ABC', 24, 'M' UNION ALL SELECT 'ABC', 24,'M' UNION ALL SELECT 'LMN', 27, 'M' UNION ALL SELECT 'LMN', 27, 'M' UNION ALL SELECT 'LMN', 27, 'M' UNION ALL SELECT 'PQRS', 25, 'F' UNION ALL SELECT 'XYZ', 24, 'M' UNION ALL SELECT 'XYZ', 25, 'M'DECLARE @hash VARCHAR(80), @sex VARCHAR(1)UPDATE @sample SET @sex=CASE WHEN COALESCE(@hash,'') <>TheName+CONVERT(VARCHAR(5),Identifier)+sex THEN 'd' ELSE SEX END, @hash= TheName+CONVERT(VARCHAR(5),Identifier)+sex, Sex = @sexDELETE FROM @sample WHERE sex='d'SELECT * FROM @sample[/font][/quote]The quirky update trick isn't really reliable without a Clustered index. For instance, when I execute you code, thi sis what I get:[code="plain"]TheName Identifier sex------- ----------- ----ABC 24 MLMN 27 MLMN 27 M[/code]Mon, 02 Nov 2009 18:26:40 GMTRBarryYounghttp://www.sqlservercentral.com/Forums/Topic546486-1226-1.aspx[quote][b]hmi (10/30/2009)[/b][hr]Good brain training but are the restrictions as mentioned realistic? Which company would definitely not want to use a temp table or table variable?[/quote]Quite right, it's really just brain training. :-)Mon, 02 Nov 2009 18:09:25 GMTRBarryYounghttp://www.sqlservercentral.com/Forums/Topic546486-1226-1.aspxOne final update to my solution. Using CTE's, the output query is much easier to understand.Here is the complete script:create table #temp(name varchar(4),age int, sex varchar(1))insert #tempselect 'ABC',24,'M'union allselect 'ABC',24,'M'union allselect 'LMN', 27, 'M'union allselect 'LMN', 27, 'M'union allselect 'LMN', 27, 'M'union allselect 'PQRS', 25, 'F'union allselect 'XYZ', 24, 'M'union allselect 'XYZ', 25, 'M';with tmpas(select name, age, sex, row_number() over (order by name) as identfrom #temp)select a.name, a.age, a.sex, a.identfrom tmp a inner join(select b.name, b.age, b.sex, min(b.ident) as identfrom tmp bgroup by b.name, b.age, b.sexhaving count(*) > 1) c on a.name = c.name and a.age = c.age and a.sex = c.sex and a.ident > c.identdrop table #temp--***************************The desired output that is obtained from executing this script is:ABC 24 M 2LMN 27 M 4LMN 27 M 5Mon, 02 Nov 2009 11:41:35 GMTJLSSCHhttp://www.sqlservercentral.com/Forums/Topic546486-1226-1.aspxThanks for your comments. The following modified solution works when you use the row_number() function to number the rows in an output query.For the following query, the output for "row_number() over (order by name) as ident" will produce a column with the name of "ident" and a monotonically increasing integer value starting with 1. This is functionally the same as an identity column with a seed of 1!select name, age, sex, row_number() over (order by name) as identfrom #tempHere is the complete script:create table #temp(name varchar(4),age int, sex varchar(1))insert #tempselect 'ABC',24,'M'union allselect 'ABC',24,'M'union allselect 'LMN', 27, 'M'union allselect 'LMN', 27, 'M'union allselect 'LMN', 27, 'M'union allselect 'PQRS', 25, 'F'union allselect 'XYZ', 24, 'M'union allselect 'XYZ', 25, 'M'select a.name, a.age, a.sex, a.identfrom( select name, age, sex, row_number() over (order by name) as ident from #temp) a inner join( select b.name, b.age, b.sex, min(b.ident) as ident from ( select name, age, sex, row_number() over (order by name) as ident from #temp ) b group by b.name, b.age, b.sex having count(*) > 1) c on a.name = c.name and a.age = c.age and a.sex = c.sex and a.ident > c.identdrop table #temp--***************************The desired output that is obtained from executing this script is:ABC 24 M 2LMN 27 M 4LMN 27 M 5I hope that this helps.Mon, 02 Nov 2009 10:49:59 GMTJLSSCHhttp://www.sqlservercentral.com/Forums/Topic546486-1226-1.aspxGreat one....Mon, 02 Nov 2009 09:28:17 GMTAnipaulhttp://www.sqlservercentral.com/Forums/Topic546486-1226-1.aspxGreat article. I really liked the 'go back to the 70' part. Sometimes when I see how stuff is done just because the computer can handle the load it drives me nuts. Mon, 02 Nov 2009 08:35:02 GMTJacekOhttp://www.sqlservercentral.com/Forums/Topic546486-1226-1.aspxI have not seen this solution in the above list. Not fancy, but performs the initial request to delete duplicate rows leaving the initial entry, with minimal resource impact (especialy disk space) and no cursors, additional table/column, while not modifying the data?????:DECLARE @NM VARCHAR(50), @Age TINYINT, @Sex CHAR(1);SELECT @NM=Name, @Age = Age, @Sex = Sex FROM dbo.xSource GROUP BY Name, Age, Sex HAVING COUNT(*) > 1WHILE @NM IS NOT NULLBEGIN DELETE TOP (1) FROM dbo.xSource WHERE Name = @NM AND Age = @Age AND Sex = @Sex SELECT @NM = NULL SELECT @NM=Name, @Age = Age, @Sex = Sex FROM dbo.xSource GROUP BY Name, Age, Sex HAVING COUNT(*) > 1ENDMon, 02 Nov 2009 07:16:17 GMTStephen.Richardsonhttp://www.sqlservercentral.com/Forums/Topic546486-1226-1.aspxThis would select (or delete) everything that is duplicated. The requirement was to delete only the first occurrence of a duplicate, or the only occurrence of any unique rows.The "first" is obviously irrelevant given there is no order and they are duplicates so I read that as "one of"[quote][b]JLSSCH (11/1/2009)[/b][hr]Maybe I'm missing something, but the query the original poster was looking for is simple with the appropriate GROUP BY and HAVING clauses.SELECT Name, Age, Sex FROM SourceGROUP BY Name, Age, SexHAVING COUNT(*) > 1This ensures that the output from the query includes only a row for duplicate rows. Note no row will be output for a unique row.[/quote]Mon, 02 Nov 2009 03:08:55 GMTparodyhttp://www.sqlservercentral.com/Forums/Topic546486-1226-1.aspxMaybe I'm missing something, but the query the original poster was looking for is simple with the appropriate GROUP BY and HAVING clauses.SELECT Name, Age, Sex FROM SourceGROUP BY Name, Age, SexHAVING COUNT(*) > 1This ensures that the output from the query includes only a row for duplicate rows. Note no row will be output for a unique row.Sun, 01 Nov 2009 17:20:58 GMTJLSSCHhttp://www.sqlservercentral.com/Forums/Topic546486-1226-1.aspxBarry, someone sent me this link again, although I'd read it the last time around, and your description of the problem got me to reminiscing. It takes me back to the days of fixed file extents on disk. Probably the primary reason why you had to do an update in place was that the rest of the disk was spoken for. If things were really tight though, you might even have written a loop to de-dupe one set of identical rows at a time. There were so many hardware limitations to work around, and I'd all but forgotten them. Thanks for taking me back to the "bad old days".Sat, 31 Oct 2009 21:07:47 GMTThe Dixie Flatlinehttp://www.sqlservercentral.com/Forums/Topic546486-1226-1.aspx[quote][b]Aaron Cabrele (10/31/2009)[/b][hr]It says no [i]additional[/i] identity column so that doesn't outrule the hidden one that already exists... As for adding a guid it depends on how strictly you say what is an identity column - is that simply a column with the identity property, therefor any other column like uniqueidentifier can be added?...!I love the way, once most or all possibilities are exhausted, we try and re-interpret the question, bend the rules...[/quote]LOL. Well, the objective is to find the simplest solution within the rules given. As with any game or puzzle, a large part of the solution is knowing what the boundaries really are.Sat, 31 Oct 2009 14:44:43 GMTThomas-282729http://www.sqlservercentral.com/Forums/Topic546486-1226-1.aspxI wanted to come up with a solution of my own with reading just the statement of the challenge. So, here it is. I took me about 10-15 minutes to have the flash of inspiration. Now I will go back and read the article past the "Cursors? Foiled Again" section and the discussion thread.[font="Courier New"]create table stat ( Name varchar(50), Age int, Sex char(1));goset nocount on;insert stat values ('ABC', 24, 'M');insert stat values ('ABC', 24, 'M');insert stat values ('LMN', 27, 'M');insert stat values ('LMN', 27, 'M');insert stat values ('LMN', 27, 'M');insert stat values ('PQRS', 25, 'F');insert stat values ('XYZ', 24, 'M'); -- These are not dupli-insert stat values ('XYZ', 24, 'M'); -- cates in the articleset nocount off;select * from stat order by Name;goset nocount on;declare @mo int, @i int;select @mo = MAX(s.Occurances)from ( select Name, Age, Sex, COUNT(*) Occurances from stat where Age < 1000 group by Name, Age, Sex) s;set @i = 2;while (@i <= @mo) begin insert stat select Name, Age + 1000, Sex from stat where Age < 1000 group by Name, Age, Sex having COUNT(*) >= @i; set @i = @i + 1;enddelete stat where Age < 1000;update stat set Age = Age - 1000;set nocount off;goselect * from stat order by Name;godrop table stat;go[/font]Sat, 31 Oct 2009 12:01:55 GMTJediSQLhttp://www.sqlservercentral.com/Forums/Topic546486-1226-1.aspxIt says no [i]additional[/i] identity column so that doesn't outrule the hidden one that already exists... As for adding a guid it depends on how strictly you say what is an identity column - is that simply a column with the identity property, therefor any other column like uniqueidentifier can be added?...!I love the way, once most or all possibilities are exhausted, we try and re-interpret the question, bend the rules...[quote][b]Thomas-282729 (10/30/2009)[/b][hr]The original article states that you cannot use an identity column but says nothing about adding columns in general. Doesn't the entire issue come down to uniquely identifying each row? If so, why not simply add a guid column and fill it with newId()? The other solutions effectively do the same except into a table variable or by munging the data.[/quote]Sat, 31 Oct 2009 05:19:05 GMTparodyhttp://www.sqlservercentral.com/Forums/Topic546486-1226-1.aspxThe original article states that you cannot use an identity column but says nothing about adding columns in general. Doesn't the entire issue come down to uniquely identifying each row? If so, why not simply add a guid column and fill it with newId()? The other solutions effectively do the same except into a table variable or by munging the data.Fri, 30 Oct 2009 21:25:37 GMTThomas-282729http://www.sqlservercentral.com/Forums/Topic546486-1226-1.aspxExcellent link - that will come in very handy.And makes my solution to the article much simpler, no need to add a fake ID or any preparation just one simple delete of the first or only record:DELETE aFROM Play aWHERE replace(%%LockRes%%,':','') =(SELECT min(replace(%%LockRes%%,':',''))FROM Play bWHERE a.TheName = b.TheNameAND a.Identifier = b.IdentifierAND a.sex = b.sex[quote][b]jghali (10/30/2009)[/b][hr]Funny how this article just came out today... I was actually on a similar thread last week and someone came up with a great solution... My personal opinion the article is interesting but ... hmmm...How about these solutions from this thread... http://www.sqlservercentral.com/Forums/Topic793765-145-1.aspxThere's an undocumented identity key for every row of any table that can be used... in one simple delete statement you can delete the duplicate rows...I was amazed... Check it out.Thanks[/quote]Fri, 30 Oct 2009 15:56:15 GMTparodyhttp://www.sqlservercentral.com/Forums/Topic546486-1226-1.aspx[quote][b]RBarryYoung (8/5/2008)[/b][hr][quote][b]Antares686 (8/5/2008)[/b][hr]But my first impression was the first example for the contact numbers. I noticed you made 4 passes over the data instead of just the one. And even thou you option works the issue the customer faced was they had two seperate updates which caused the issue. If both had been handled in the same UPDATE they issue does not occurr and I would have done something like this[code]BEGIN TransactionUPDATE CONTACT_NUMBERSSET CallOrder = (CASE WHEN CallOrder = 1 THEN 2 ELSE 1 END)COMMIT Transaction[/code]as this makes one pass across the data and adjust all records at the same time the conflict does not occurr. [/quote]True, however, recall that I qualified this example in the article:[quote][i]And yes, I do know that there are other ways to do this correctly, especially with a CASE function. For reasons that I cannot get into, that was not an option here.[/i][/quote][/quote]See, my old time itch is boolean algebra. This solution doesn't really use it, but the concept is the same.[code="sql"]UPDATE CONTACT_NUMBERSSET CallOrder = -1*CallOrder + 3[/code]1 becomes -1 + 3 = 22 becomes -2 + 3 = 1--JimFiveFri, 30 Oct 2009 14:31:40 GMTJames Goodwinhttp://www.sqlservercentral.com/Forums/Topic546486-1226-1.aspx(Oops!! correction at the end of the post in bold.)Interesting Read! Good article! and a clever use of an "old" method.Comment on the "The Temp In-Place Method". In those days we also worried about the machine cycles used, so for the example described in this part of the article, we would have limited the number of updates to 3: first, update '1' to '-2'; second, update '2' to '1'; and final pass to update '-2' to [b]'2'[/b].Fri, 30 Oct 2009 13:21:32 GMTJP-470http://www.sqlservercentral.com/Forums/Topic546486-1226-1.aspxInteresting Read! Good article! and a clever use of an "old" method.Comment on the "The Temp In-Place Method". In those days we also worried about the machine cycles used, so for the example described in this part of the article, we would have limited the number of updates to 3: first, update '1' to '-2'; second, update '2' to '1'; and final pass to update '-2' to '1'.Fri, 30 Oct 2009 13:12:23 GMTJP-470http://www.sqlservercentral.com/Forums/Topic546486-1226-1.aspx[quote][b]Jasmine D. Adamson (10/30/2009)[/b][hr][quote][b]arms.dan (10/30/2009)[/b][hr]Your off-hand comment about naming the field "Gender" rather than "Sex" piqued my curiosity. Why? As I understand the words, sex means biological differences: chromosomes and sexual organs. Gender refers to the characteristics a society or culture delineates as masculine or feminine. There's some blurring of the definitions, but I think they generally hold. So why do you prefer Gender for the column label?[/quote]It is in the interest of calling things what they are. In a medical file, it's probably more appropriate to use 'sex' but in a demographics file, it's probably more informative to use 'gender' - if both of these items were in my medical files, they would have different answers. Since I purchase a lot of cosmetics and other products, someone looking at sales information would want to consider me female, which is a gender.[/quote]Jasmine,This may be a generational issue, but last time I checked, most sex-differentiation categories (i.e., Y vs X chromosome) give you the options of male or female. So I beg to differ whether female characterizes only a gender. (And I hope this does not engender much more debate - we are moving far afield from the original brain-teaser).Fri, 30 Oct 2009 11:00:54 GMTsteve smith-401573http://www.sqlservercentral.com/Forums/Topic546486-1226-1.aspx[quote][b]arms.dan (10/30/2009)[/b][hr]Your off-hand comment about naming the field "Gender" rather than "Sex" piqued my curiosity. Why? As I understand the words, sex means biological differences: chromosomes and sexual organs. Gender refers to the characteristics a society or culture delineates as masculine or feminine. There's some blurring of the definitions, but I think they generally hold. So why do you prefer Gender for the column label?[/quote]It is in the interest of calling things what they are. In a medical file, it's probably more appropriate to use 'sex' but in a demographics file, it's probably more informative to use 'gender' - if both of these items were in my medical files, they would have different answers. Since I purchase a lot of cosmetics and other products, someone looking at sales information would want to consider me female, which is a gender.Fri, 30 Oct 2009 10:35:32 GMTJasmine D. Adamsonhttp://www.sqlservercentral.com/Forums/Topic546486-1226-1.aspxFriday fun...:-)[quote][b]RBarryYoung (8/5/2008)[/b][hr][quote][b]Antares686 (8/5/2008)[/b][hr]But my first impression was the first example for the contact numbers. I noticed you made 4 passes over the data instead of just the one. And even thou you option works the issue the customer faced was they had two seperate updates which caused the issue. If both had been handled in the same UPDATE they issue does not occurr and I would have done something like this[code]BEGIN TransactionUPDATE CONTACT_NUMBERSSET CallOrder = (CASE WHEN CallOrder = 1 THEN 2 ELSE 1 END)COMMIT Transaction[/code]as this makes one pass across the data and adjust all records at the same time the conflict does not occurr. [/quote]True, however, recall that I qualified this example in the article:[quote][i]And yes, I do know that there are other ways to do this correctly, especially with a CASE function. For reasons that I cannot get into, that was not an option here.[/i][/quote][/quote]I don' need no stinking CASE...:-P[code="sql"]UPDATE CONTACT_NUMBERSSET CallOrder = CallOrder | 2 - 1[/code]Then again this might not have been right for that customer either...;-)Fri, 30 Oct 2009 10:13:52 GMTTom Garthhttp://www.sqlservercentral.com/Forums/Topic546486-1226-1.aspxI know this is an old article, and maybe someone already offered this solution, but to be honest the comment list is just too long for me to read it all...So I used your approach, but instead of the sex column, I used the age, which I assumed was an integer.The 'sequence' is multiplied by 1000 and added to the age, then we just reverse back at the end:insert into source(name, age, sex)select s.name, s.age + (t.number * 1000) as newage, s.sexfrom dbo.source sjoin tally ton t.number <= ( select count(*) from source srccnt where srccnt.name = s.name and srccnt.name = s.name and srccnt.name = s.name)and t.number > 1group by s.name, s.age, s.sex ,t.numberdelete from source where age < 1000update sourceset age = age % 1000Fri, 30 Oct 2009 09:34:18 GMTWanderlei Santoshttp://www.sqlservercentral.com/Forums/Topic546486-1226-1.aspxActually, given that it's not a relational problem, a procedural solution is likely correct, unless there's another way that I'm mostly ignorant on (e.g. functional) to solve it .Now this was "just for fun"; which is fine. We all like a good "clever ugly" hack [b]as brain candy[/b]!You didn't hold that (a) the data made sense or (b) that SQL was truly the best tool to solve it. Cool.Had it not been a puzzle whose constraints were simply to drive the thought process (rather than actually solve the stated problem quickly), I'd say:Given how often procedural programmers misuse SQL by using it [b]like it was a set of commands for a COBOL tape merge[/b], it pains me to see the case when a good "what not how" programmer goes along with forcing SQL into such unnatural contortions.SQL (even with its flaws) is for databases, not for reading sorted, line oriented files that some misguided soul who has mistaken an RDBMS for a file system. It's much rarer than the inverse, of course. If the data had not been stored in a RDBMS, we'd all have been thinking a command line pipe (Unix or PowerShell) or Perl, a quick C or VB hack to do what's actually a data cleansing job. So my solution is to ignore that it's been stored in SQL Server: bcp it out, pipe it through a 3 line Perl script, and bcp it in (or better, move the result to some appropriate appliance). It was an enjoyable read.Fri, 30 Oct 2009 08:05:51 GMTRoger L Reidhttp://www.sqlservercentral.com/Forums/Topic546486-1226-1.aspxWhat a great thought experiment! :smooooth:It was fun to watch the problem solving unfold and the answer develop.These are the articles that keep me coming back.Thanks a ton for sharing;GregFri, 30 Oct 2009 07:48:55 GMTBlackHawk-17http://www.sqlservercentral.com/Forums/Topic546486-1226-1.aspxThanks for the thought provoking article R, and 5 stars for a good read!I've used the [i][b]Temp In-Place [/b][/i] method in the past. Sometimes to fix my own mistakes. I never knew it had a name.An aside: Assuming tinyint for the age would be incorrect, since the primary solution in the article drove the column negative.Fri, 30 Oct 2009 07:46:16 GMTTom Garthhttp://www.sqlservercentral.com/Forums/Topic546486-1226-1.aspxBrings back many fond memories, or nightmares back then. Excellent article, which has the opportunity to not only show alternatives to how we do things now, but also provides opportunities to expand our knowledgebase with alternative examples. Never know when one may run into some of these opportunities.Fri, 30 Oct 2009 07:41:51 GMTsjimmohttp://www.sqlservercentral.com/Forums/Topic546486-1226-1.aspxYour off-hand comment about naming the field "Gender" rather than "Sex" piqued my curiosity. Why? As I understand the words, sex means biological differences: chromosomes and sexual organs. Gender refers to the characteristics a society or culture delineates as masculine or feminine. There's some blurring of the definitions, but I think they generally hold. So why do you prefer Gender for the column label?Fri, 30 Oct 2009 07:37:13 GMTarms.danhttp://www.sqlservercentral.com/Forums/Topic546486-1226-1.aspx[quote][b]jghali (10/30/2009)[/b][hr]Funny how this article just came out today... I was actually on a similar thread last week and someone came up with a great solution... My personal opinion the article is interesting but ... hmmm...How about these solutions from this thread... http://www.sqlservercentral.com/Forums/Topic793765-145-1.aspxThere's an undocumented identity key for every row of any table that can be used... in one simple delete statement you can delete the duplicate rows...I was amazed... Check it out.Thanks[/quote]Please read the thread its not about deleting duplicates its about keeping them :-DFri, 30 Oct 2009 07:23:55 GMTShaun McGuilehttp://www.sqlservercentral.com/Forums/Topic546486-1226-1.aspxThe orginal request was to purge the system of "duplicate" rows. Some rows are duplicated and some are not. Additionaly some names lie Terry may be either a male or female therefore you could have a 30 year old Terry male and female therefore not duplicate rows. If you do an update you change all rows matching criteria (I did not try update top 1). Personaly I like security, NO MISTAKES. In production the absolute worst word in the the human lanquage is "oooopppssss", or "I think".Fri, 30 Oct 2009 07:23:40 GMTStephen.Richardson