Just For Fun: An Impossible Delete

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » Article Discussions » Article Discussions by Author » Discuss content posted by RBarryYoung » Just For Fun: An Impossible Delete

157 posts, Page 14 of 16

«««12 13 14 15 16 »»»

Just For Fun: An Impossible Delete

Rate Topic

Display Mode

Topic Options

Author

Message

James Goodwin

Posted Friday, October 30, 2009 2:31 PM

Ten Centuries

Group: General Forum Members
Last Login: 2 days ago @ 2:52 PM
Points: 1,322, Visits: 1,071

RBarryYoung (8/5/2008)

Antares686 (8/5/2008)

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

BEGIN Transaction
UPDATE CONTACT_NUMBERS
SET CallOrder = (CASE WHEN CallOrder = 1 THEN 2 ELSE 1 END)
COMMIT Transaction

as this makes one pass across the data and adjust all records at the same time the conflict does not occurr.

True, however, recall that I qualified this example in the article:

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.

See, my old time itch is boolean algebra. This solution doesn't really use it, but the concept is the same.

UPDATE CONTACT_NUMBERS
SET CallOrder = -1*CallOrder + 3

1 becomes -1 + 3 = 2
2 becomes -2 + 3 = 1
--
JimFive

Post #811868

parody

Posted Friday, October 30, 2009 3:56 PM

SSC Rookie

Group: General Forum Members
Last Login: Monday, July 16, 2012 3:02 AM
Points: 30, Visits: 162

Excellent 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 a
FROM Play a
WHERE replace(%%LockRes%%,':','') =
(SELECT min(replace(%%LockRes%%,':',''))
FROM Play b
WHERE a.TheName = b.TheName
AND a.Identifier = b.Identifier
AND a.sex = b.sex

jghali (10/30/2009)

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.aspx

There'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

Post #811905

Thomas-282729

Posted Friday, October 30, 2009 9:25 PM

SSC Journeyman

Group: General Forum Members
Last Login: Tuesday, May 21, 2013 4:59 PM
Points: 89, Visits: 471

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.

Post #811957

parody

Posted Saturday, October 31, 2009 5:19 AM

SSC Rookie

Group: General Forum Members
Last Login: Monday, July 16, 2012 3:02 AM
Points: 30, Visits: 162

It says no additional 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...

Thomas-282729 (10/30/2009)

Post #812005

JediSQL

Posted Saturday, October 31, 2009 12:01 PM

SSC Rookie

Group: General Forum Members
Last Login: Tuesday, May 21, 2013 12:18 PM
Points: 27, Visits: 102

I 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.

create table stat (
Name varchar(50), Age int, Sex char(1)
);
go

set 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 article
set nocount off;
select * from stat order by Name;
go

set 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;
end

delete stat where Age < 1000;
update stat set Age = Age - 1000;
set nocount off;
go

select * from stat order by Name;
go

drop table stat;
go

Sincerely,
Daniel

Post #812036

Thomas-282729

Posted Saturday, October 31, 2009 2:44 PM

SSC Journeyman

Group: General Forum Members
Last Login: Tuesday, May 21, 2013 4:59 PM
Points: 89, Visits: 471

Aaron Cabrele (10/31/2009)

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.

Post #812053

The Dixie Flatline

Posted Saturday, October 31, 2009 9:07 PM

Hall of Fame

Group: General Forum Members
Last Login: Today @ 1:52 PM
Points: 3,790, Visits: 5,548

Barry, 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".

__________________________________________________

Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? -- Stephen Stills

Post #812068

JLSSCH

Posted Sunday, November 01, 2009 5:20 PM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Saturday, June 16, 2012 11:09 PM
Points: 199, Visits: 55

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 Source
GROUP BY Name, Age, Sex
HAVING COUNT(*) > 1

This ensures that the output from the query includes only a row for duplicate rows. Note no row will be output for a unique row.

Post #812168

parody

Posted Monday, November 02, 2009 3:08 AM

SSC Rookie

Group: General Forum Members
Last Login: Monday, July 16, 2012 3:02 AM
Points: 30, Visits: 162

This 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"

JLSSCH (11/1/2009)

Post #812275

Stephen.Richardson

Posted Monday, November 02, 2009 7:16 AM

SSC Rookie

Group: General Forum Members
Last Login: Friday, December 21, 2012 6:44 AM
Points: 26, Visits: 133

I 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(*) > 1

WHILE @NM IS NOT NULL
BEGIN
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(*) > 1
END

Post #812362

« Prev Topic | Next Topic »

157 posts, Page 14 of 16

«««12 13 14 15 16 »»»

Permissions