Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase «««1213141516»»»

Just For Fun: An Impossible Delete Expand / Collapse
Author
Message
Posted Friday, October 30, 2009 2:31 PM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Monday, January 27, 2014 10:14 AM
Points: 1,322, Visits: 1,091
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
Posted Friday, October 30, 2009 3:56 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Tuesday, October 21, 2014 4:35 AM
Points: 30, Visits: 189
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
Posted Friday, October 30, 2009 9:25 PM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Friday, October 10, 2014 2:06 PM
Points: 89, Visits: 482
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
Posted Saturday, October 31, 2009 5:19 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Tuesday, October 21, 2014 4:35 AM
Points: 30, Visits: 189
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)
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 #812005
Posted Saturday, October 31, 2009 12:01 PM


SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Tuesday, September 23, 2014 7:28 PM
Points: 47, Visits: 140
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
Posted Saturday, October 31, 2009 2:44 PM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Friday, October 10, 2014 2:06 PM
Points: 89, Visits: 482
Aaron Cabrele (10/31/2009)
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...


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
Posted Saturday, October 31, 2009 9:07 PM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Sunday, August 17, 2014 3:10 PM
Points: 2,787, Visits: 6,098
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
Posted Sunday, November 1, 2009 5:20 PM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Saturday, June 16, 2012 11:09 PM
Points: 202, 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
Posted Monday, November 2, 2009 3:08 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Tuesday, October 21, 2014 4:35 AM
Points: 30, Visits: 189
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)
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 #812275
Posted Monday, November 2, 2009 7:16 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC 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 »

Add to briefcase «««1213141516»»»

Permissions Expand / Collapse