problems with this query - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2012 Forums

Transact-SQL (2012)

problems with this query

Author

Topic

hells
Starting Member

10 Posts

Posted - 2014-04-02 : 06:12:08

help me please improve this query, it does not seem to work ...

USE [CHARACTER_01_DBF]
GO
/****** Object: StoredProcedure [dbo].[uspDeleteDupes] Script Date: 04/02/2014 13:01:04 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE [dbo].[uspDeleteDupes]
AS
SET NOCOUNT ON

SELECT inv0.m_dwSerialNumber, inv0.m_idPlayer, inv0.m_dwObjId, ROW_NUMBER() OVER(ORDER BY inv0.m_dwSerialNumber) AS rn, IDENTITY( int ) AS idcol INTO #TMP_TABLE FROM INVENTORY_TBL as inv0
INNER JOIN INVENTORY_TBL as inv1 ON inv0.m_nItemNum = 1 AND ( inv0.m_dwObjId != inv1.m_dwObjId
OR inv0.m_idPlayer != inv1.m_idPlayer ) AND inv0.m_dwSerialNumber = inv1.m_dwSerialNumber AND inv0.m_dwItemId = inv1.m_dwItemId
WHERE inv0.m_dwItemId NOT IN ( 900018,21,23,500,502,510,506,60372,10226,2001,10226,4400,10226,23,10226,508,2001,512,501,504,4400,60371 )
AND inv0.m_dwItemId NOT IN ( SELECT DISTINCT chk0.m_dwItemId FROM INVENTORY_TBL as chk0 WHERE inv0.m_dwItemId = chk0.m_dwItemId AND chk0.m_nItemNum != 1 )
AND inv0.m_nItemNum = 1 GROUP BY inv0.m_dwSerialNumber, inv0.m_idPlayer, inv0.m_dwObjId

/*SELECT * FROM #TMP_TABLE as t1 WHERE t1.idcol NOT IN ( SELECT MIN( idcol ) FROM #TMP_TABLE
WHERE m_dwSerialNumber = t1.m_dwSerialnumber )*/
DELETE #TMP_TABLE FROM #TMP_TABLE as t1 WHERE t1.idcol NOT IN ( SELECT MIN( idcol ) FROM #TMP_TABLE
WHERE m_dwSerialNumber = t1.m_dwSerialnumber )

DELETE INVENTORY_TBL FROM INVENTORY_TBL as t2 INNER JOIN #TMP_TABLE as t1 ON t2.m_dwSerialNumber = t1.m_dwSerialnumber AND t2.m_idPlayer = t1.m_idPlayer
AND t2.m_dwObjId = t1.m_dwObjId

RETURN

SET NOCOUNT OFF

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2014-04-02 : 06:52:23

help us please to help you to improve this query, with the given information it does not seem to work ...

Too old to Rock'n'Roll too young to die.

hells
Starting Member

10 Posts

Posted - 2014-04-02 : 07:03:24

we try to delete the duplicated rows from inventory_tbl where the ItemID and serialnumber are same but not where itemnumber > 1 from any of the dupes so that we do not delete stackable items .

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2014-04-02 : 07:12:13

test this


delete dt
from
(select row_number() over (partition by ItemID,serialnumber order by ItemID)rn, * from  inventory_tbl t1 
 where not exists(select * from  inventory_tbl t2 
                 where t2.ItemID = t1.ItemID and
                       t2.serialnumber = t1.serialnumber and
                       t2.itemnumber > 1)
)dt
where rn > 1

Too old to Rock'n'Roll too young to die.

hells
Starting Member

10 Posts

Posted - 2014-04-02 : 07:20:02

thanks a lot

also a question

if there are rows that repeat them selfs , that are duped itemID with same seralnumber and the item number is > 1 at any of them , i do not wat any of those rows to be deleted because that means those items are stackable and should not be deleted

hells
Starting Member

10 Posts

Posted - 2014-04-02 : 07:28:35

also this is what i am using to check if there are any more dupes left , it seems really really slow, am i failing somewhere ?

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2014-04-02 : 07:42:57

quote:
Originally posted by hells

thanks a lot

also a question

if there are rows that repeat them selfs , that are duped itemID with same seralnumber and the item number is > 1 at any of them , i do not wat any of those rows to be deleted because that means those items are stackable and should not be deleted

I think in my solution I have considered this...

Too old to Rock'n'Roll too young to die.

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2014-04-02 : 07:43:54

quote:
Originally posted by hells

also this is what i am using to check if there are any more dupes left , it seems really really slow, am i failing somewhere ?

I don't know HOW you are doing your check so I can't know if you are failing somewhere.

Too old to Rock'n'Roll too young to die.

hells
Starting Member

10 Posts

Posted - 2014-04-02 : 07:44:06

thanks
can u please now check my checkdupes query ?
its running since 25 minutes and did not produce any result

ALTER PROCEDURE [dbo].[uspCheckDupes]
AS
SET NOCOUNT ON

SELECT inv0.m_dwSerialNumber, inv0.m_idPlayer, inv1.m_dwSerialNumber, inv1.m_idPlayer, inv0.m_dwItemId FROM INVENTORY_TBL as inv0
INNER JOIN INVENTORY_TBL as inv1 ON inv0.m_nItemNum = 1 AND ( inv0.m_dwObjId != inv1.m_dwObjId
OR inv0.m_idPlayer != inv1.m_idPlayer ) AND inv0.m_dwSerialNumber = inv1.m_dwSerialNumber AND inv0.m_dwItemId = inv1.m_dwItemId
WHERE inv0.m_dwItemId NOT IN ( 900018,21,23,500,502,510,506,60372,10226,2001,10226,4400,10226,23,10226,508,2001,512,501,504,4400,60371 )
AND inv0.m_dwItemId NOT IN ( SELECT chk0.m_dwItemId FROM INVENTORY_TBL as chk0 WHERE inv0.m_dwItemId = chk0.m_dwItemId AND chk0.m_nItemNum != 1 )
AND inv0.m_nItemNum = 1
ORDER BY inv0.m_dwSerialNumber
RETURN

hells
Starting Member

10 Posts

Posted - 2014-04-02 : 07:46:15

well
i am checking this :

checking all rows with same itemID and same serialnumber and where the item number does not go above > 1 in any of the rows

that way i find if people duped any items but right now its running since 25 minutes and no result was produced to see if there still are dupes ...

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2014-04-02 : 07:53:13

this?


select ItemID,serialnumber,count(*) as numRows
from inventory_tbl t1
where ItemID not in ( 900018,21,23,500,502,510,506,60372,10226,2001,10226,4400,10226,23,10226,508,2001,512,501,504,4400,60371 )
and not exists(select * from  inventory_tbl t2 
                 where t2.ItemID = t1.ItemID and
                       t2.serialnumber = t1.serialnumber and
                       t2.itemnumber > 1)
group by ItemID,serialnumber 
having count(*) > 1

Too old to Rock'n'Roll too young to die.

hells
Starting Member

10 Posts

Posted - 2014-04-02 : 08:38:37

i thank you very very much , this simple procedures you wrote are extremely good and working 100%
i can not thank you enough for your time and patience with me !

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2014-04-02 : 08:47:10

you are welcome

Too old to Rock'n'Roll too young to die.

hells
Starting Member

10 Posts

Posted - 2014-04-02 : 17:18:08

can u make the delete not delete the item ids in this list please ?
delete dt
from
(select row_number() over (partition by ItemID,serialnumber order by ItemID)rn, * from inventory_tbl t1
where not exists(select * from inventory_tbl t2
where t2.ItemID = t1.ItemID and
t2.serialnumber = t1.serialnumber and
t2.itemnumber > 1)
)dt
where rn > 1

where m_dwItemId not in ( 0,21,23,500,502,510,506,60372,10226,2001,10226,4400,10226,23,10226,508,2001,512,501,504,4400,60371 )

hells
Starting Member

10 Posts

Posted - 2014-04-02 : 17:21:09

i made it like this
please confirm that i did not fail

USE [CHARACTER_01_DBF]
GO
/****** Object: StoredProcedure [dbo].[uspDeleteDupes] Script Date: 04/02/2014 13:01:04 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE [dbo].[uspDeleteDupes]
AS
SET NOCOUNT ON

delete dt
from
(select row_number() over (partition by m_dwItemId,m_dwSerialNumber order by m_dwItemId)rn, * from inventory_tbl t1
where not exists(select * from inventory_tbl t2
where t2.m_dwItemId = t1.m_dwItemId and
t2.m_dwSerialNumber = t1.m_dwSerialNumber and
t2.m_nItemNum > 1)
)dt
where rn > 1 and m_dwItemId not in ( 0,21,23,500,502,510,506,60372,10226,2001,10226,4400,10226,23,10226,508,2001,512,501,504,4400,60371 )

RETURN

SET NOCOUNT OFF

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2014-04-03 : 03:41:21

test this please:


delete dt
from
(select row_number() over (partition by m_dwItemId,m_dwSerialNumber order by m_dwItemId)rn, * from inventory_tbl t1 
 where m_dwItemId not in ( 0,21,23,500,502,510,506,60372,10226,2001,10226,4400,10226,23,10226,508,2001,512,501,504,4400,60371 )
 and not exists(select * from inventory_tbl t2 
 where t2.m_dwItemId = t1.m_dwItemId and
 t2.m_dwSerialNumber = t1.m_dwSerialNumber and
 t2.m_nItemNum > 1)
)dt
where rn > 1

Too old to Rock'n'Roll too young to die.

hells
Starting Member

10 Posts

Posted - 2014-04-03 : 05:31:45

seems to work fine thank you

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources