databases. They modified the database to create duplicate SKU values.
Each SKU value has a different Name column value.
What I need to do is delete the shorter value.
SKU Name
111 Glass Vase
111 Glass
333 Apple
so I need to get rid of SKU 111 w/ the Name value of Glass.
What I have done is created a view that give me a listing of all the
row values with duplicate SKUs, but I am not sure how to do the Len
comparison and return the shorter value.
Any help is appreciated.
Thanks!a query like:
select p.SKU, p.Name
from Products P
inner join (Select SKU, len(Name) as L from Products group by SKU) P2
on P.sku = p1.sku and len(p.Name) = p2.L
but this query keep duplicated values or names with the same length.
so you can do this:
select p.SKU, min(p.Name) as Name
from Products P
inner join (Select SKU, len(Name) as L from Products group by SKU) P2
on P.sku = p1.sku and len(p.Name) = p2.L
group by p.SKU
<carpeaqua@.gmail.com> wrote in message
news:1153932460.568433.293330@.s13g2000cwa.googlegroups.com...
>a client of mine caused a bit of an issue with one of our SQL Server
> databases. They modified the database to create duplicate SKU values.
> Each SKU value has a different Name column value.
> What I need to do is delete the shorter value.
> SKU Name
> 111 Glass Vase
> 111 Glass
> 333 Apple
> so I need to get rid of SKU 111 w/ the Name value of Glass.
> What I have done is created a view that give me a listing of all the
> row values with duplicate SKUs, but I am not sure how to do the Len
> comparison and return the shorter value.
> Any help is appreciated.
> Thanks!
>|||J=E9j=E9 wrote:
> select p.SKU, min(p.Name) as Name
> from Products P
> inner join (Select SKU, len(Name) as L from Products group by SKU) P2
> on P.sku =3D p1.sku and len(p.Name) =3D p2.L
> group by p.SKU
>
I had to modify it to be like this (it was giving a group by error)
select p.SKU, min(p.Name) as Name
from dbo.Product P
inner join (Select SKU, len(Name) as L from dbo.Product group by SKU,
Name) P2
on P.sku =3D p.sku and len(p.Name) =3D p2.L
group by p.SKU
It have let it run for about 7 minutes and its not outputting anything
as of yet. There are almost 7000 records in the table, so is that to
be expected? Apologies for my ignorance, but I am not used to working
with SQL Server.
Thanks!|||Seven minutes and 7000 rows, there is a problem.
Roy
On 26 Jul 2006 10:31:32 -0700, carpeaqua@.gmail.com wrote:
>Jéjé wrote:
>> select p.SKU, min(p.Name) as Name
>> from Products P
>> inner join (Select SKU, len(Name) as L from Products group by SKU) P2
>> on P.sku = p1.sku and len(p.Name) = p2.L
>> group by p.SKU
>I had to modify it to be like this (it was giving a group by error)
>select p.SKU, min(p.Name) as Name
>from dbo.Product P
>inner join (Select SKU, len(Name) as L from dbo.Product group by SKU,
>Name) P2
>on P.sku = p.sku and len(p.Name) = p2.L
>group by p.SKU
>It have let it run for about 7 minutes and its not outputting anything
>as of yet. There are almost 7000 records in the table, so is that to
>be expected? Apologies for my ignorance, but I am not used to working
>with SQL Server.
>Thanks!|||This is a multi-part message in MIME format.
--=_NextPart_000_0979_01C6B0A2.71C31A40
Content-Type: text/plain;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
I believe that this idea will work for the problem as you have =described.
CREATE TABLE #MyTable
( RowID int IDENTITY
, SKU int
, Descript varchar(25)
)
SET NOCOUNT ON
INSERT INTO #MyTable VALUES ( 111, 'Glass Vase' )
INSERT INTO #MyTable VALUES ( 111, 'Glass' )
INSERT INTO #MyTable VALUES ( 333, 'Apple' )
DELETE #MyTable
WHERE Descript =3D ( SELECT min ( Descript )
FROM #MyTable
GROUP BY sku
HAVING count( SKU ) > 1
)
SELECT *
FROM #MyTable
DROP TABLE #MyTable
-- Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience. Most experience comes from bad judgment. - Anonymous
<carpeaqua@.gmail.com> wrote in message =news:1153932460.568433.293330@.s13g2000cwa.googlegroups.com...
>a client of mine caused a bit of an issue with one of our SQL Server
> databases. They modified the database to create duplicate SKU values.
> Each SKU value has a different Name column value.
> > What I need to do is delete the shorter value.
> > SKU Name
> 111 Glass Vase
> 111 Glass
> 333 Apple
> > so I need to get rid of SKU 111 w/ the Name value of Glass.
> > What I have done is created a view that give me a listing of all the
> row values with duplicate SKUs, but I am not sure how to do the Len
> comparison and return the shorter value.
> > Any help is appreciated.
> > Thanks!
>
--=_NextPart_000_0979_01C6B0A2.71C31A40
Content-Type: text/html;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
&
I believe that this idea will work for =the problem as you have described.
CREATE TABLE =#MyTable ( RowID =int IDENTITY , SKU int , Descript varchar(25) )
SET NOCOUNT ON
INSERT INTO #MyTable VALUES ( =111, 'Glass Vase' )INSERT INTO #MyTable VALUES ( 111, 'Glass' )INSERT INTO =#MyTable VALUES ( 333, 'Apple' ) DELETE #MyTableWHERE =Descript =3D ( SELECT min ( Descript ) &n=bsp; FROM #MyTable &=nbsp; GROUP BY sku = HAVING count( SKU ) > 1 &n=bsp; )
SELECT *FROM #MyTable
DROP TABLE =#MyTable
-- Arnie Rowland, =Ph.D.Westwood Consulting, Inc
Most good judgment comes from =experience. Most experience comes from bad judgment. - Anonymous
--=_NextPart_000_0979_01C6B0A2.71C31A40--|||Arnie, if two SKUs have the same description that is going to erase
some good data.
Roy
On Wed, 26 Jul 2006 10:58:36 -0700, "Arnie Rowland" <arnie@.1568.com>
wrote:
>I believe that this idea will work for the problem as you have described.
>CREATE TABLE #MyTable
> ( RowID int IDENTITY
> , SKU int
> , Descript varchar(25)
> )
>SET NOCOUNT ON
>INSERT INTO #MyTable VALUES ( 111, 'Glass Vase' )
>INSERT INTO #MyTable VALUES ( 111, 'Glass' )
>INSERT INTO #MyTable VALUES ( 333, 'Apple' )
>DELETE #MyTable
>WHERE Descript = ( SELECT min ( Descript )
> FROM #MyTable
> GROUP BY sku
> HAVING count( SKU ) > 1
> )
>SELECT *
>FROM #MyTable
>DROP TABLE #MyTable|||Yep, you are right. Drat, back to the drawing board...
(That wouldn't ever happen, would it?) ;-)
--
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
"Roy Harvey" <roy_harvey@.snet.net> wrote in message
news:7ebfc25dgml1a5dstv0a1retue37n3dcdu@.4ax.com...
> Arnie, if two SKUs have the same description that is going to erase
> some good data.
> Roy
> On Wed, 26 Jul 2006 10:58:36 -0700, "Arnie Rowland" <arnie@.1568.com>
> wrote:
>>I believe that this idea will work for the problem as you have described.
>>CREATE TABLE #MyTable
>> ( RowID int IDENTITY
>> , SKU int
>> , Descript varchar(25)
>> )
>>SET NOCOUNT ON
>>INSERT INTO #MyTable VALUES ( 111, 'Glass Vase' )
>>INSERT INTO #MyTable VALUES ( 111, 'Glass' )
>>INSERT INTO #MyTable VALUES ( 333, 'Apple' )
>>DELETE #MyTable
>>WHERE Descript = ( SELECT min ( Descript )
>> FROM #MyTable
>> GROUP BY sku
>> HAVING count( SKU ) > 1
>> )
>>SELECT *
>>FROM #MyTable
>>DROP TABLE #MyTable|||Here is my version of finding the rows you want to keep.
SELECT *
FROM Product as P
WHERE P.Name = (select min(S.Name) from Product as S
where P.SKU = S.SKU
and LEN(S.Name) = (select max(len(name))
from Product as X
where P.SKU = X.SKU))
Untested, of course, as is my version of the DELETE:
DELETE Product
WHERE EXISTS
(select * from Product as Z
where Product.SKU = Z.SKU
and Product.Name <> Z.Name
and LEN(Product.Name) < LEN(Z.Name))
Roy Harvey
Beacon Falls, CT|||Roy Harvey wrote:
> Here is my version of finding the rows you want to keep.
> SELECT *
> FROM Product as P
> WHERE P.Name => (select min(S.Name) from Product as S
> where P.SKU = S.SKU
> and LEN(S.Name) => (select max(len(name))
> from Product as X
> where P.SKU = X.SKU))
> Untested, of course, as is my version of the DELETE:
> DELETE Product
> WHERE EXISTS
> (select * from Product as Z
> where Product.SKU = Z.SKU
> and Product.Name <> Z.Name
> and LEN(Product.Name) < LEN(Z.Name))
This worked wonderfully. Thank you, my friend.|||Just to note that my two queries could involve different results. The
first one returned ONE row, even if the two rows had the same
(longest) length description. The DELETE only deleted rows with
shorter descriptions.
It would be worth double checking:
SELECT SKU, max(Name), min(Name), count(*)
FROM Product
GROUP BY SKU
HAVING COUNT(*) > 1
ORDER BY SKU, 2, 3
Roy
On 26 Jul 2006 11:28:48 -0700, carpeaqua@.gmail.com wrote:
>Roy Harvey wrote:
>> Here is my version of finding the rows you want to keep.
>> SELECT *
>> FROM Product as P
>> WHERE P.Name =>> (select min(S.Name) from Product as S
>> where P.SKU = S.SKU
>> and LEN(S.Name) =>> (select max(len(name))
>> from Product as X
>> where P.SKU = X.SKU))
>> Untested, of course, as is my version of the DELETE:
>> DELETE Product
>> WHERE EXISTS
>> (select * from Product as Z
>> where Product.SKU = Z.SKU
>> and Product.Name <> Z.Name
>> and LEN(Product.Name) < LEN(Z.Name))
>
>This worked wonderfully. Thank you, my friend.|||What would happen if there were 2 rows with the same SKU and the same length
[Name]?
Would they BOTH be left -or deleted, and without indication of an incomplete
update?
--
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
"Roy Harvey" <roy_harvey@.snet.net> wrote in message
news:qkdfc2t451agl5stchnp57po3gur2vj7bk@.4ax.com...
> Just to note that my two queries could involve different results. The
> first one returned ONE row, even if the two rows had the same
> (longest) length description. The DELETE only deleted rows with
> shorter descriptions.
> It would be worth double checking:
> SELECT SKU, max(Name), min(Name), count(*)
> FROM Product
> GROUP BY SKU
> HAVING COUNT(*) > 1
> ORDER BY SKU, 2, 3
> Roy
> On 26 Jul 2006 11:28:48 -0700, carpeaqua@.gmail.com wrote:
>>Roy Harvey wrote:
>> Here is my version of finding the rows you want to keep.
>> SELECT *
>> FROM Product as P
>> WHERE P.Name =>> (select min(S.Name) from Product as S
>> where P.SKU = S.SKU
>> and LEN(S.Name) =>> (select max(len(name))
>> from Product as X
>> where P.SKU = X.SKU))
>> Untested, of course, as is my version of the DELETE:
>> DELETE Product
>> WHERE EXISTS
>> (select * from Product as Z
>> where Product.SKU = Z.SKU
>> and Product.Name <> Z.Name
>> and LEN(Product.Name) < LEN(Z.Name))
>>
>>This worked wonderfully. Thank you, my friend.|||On Wed, 26 Jul 2006 11:47:07 -0700, "Arnie Rowland" <arnie@.1568.com>
wrote:
>What would happen if there were 2 rows with the same SKU and the same length
>[Name]?
>Would they BOTH be left -or deleted, and without indication of an incomplete
>update?
If they were both the longest they would both be left by the DELETE I
wrote, but only one of them would have appeared in the first query.
Roy
