Trying to erase duplicated rows

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 2008 Forums

Transact-SQL (2008)

Trying to erase duplicated rows

Author

Topic

rnavarro
Starting Member

10 Posts

Posted - 2012-09-16 : 12:41:12

Hello people, i trying to erase duplicated rows with

DECLARE TEST_CURSOR CURSOR FOR
SELECT a."Code"
FROM tla_sg2org a
WHERE a."CodeOrg" IN (SELECT b."CodeOrg"
FROM tla_sg2org b
GROUP BY b."CodeOrg", b."SGroup"
HAVING COUNT (b."SGroup") > 1)
AND a."SGroup" IN (SELECT c."SGroup"
FROM tla_sg2org c
WHERE c."CodeOrg" = a."CodeOrg"
GROUP BY c."CodeOrg", c."SGroup"
HAVING COUNT (c."SGroup") > 1 )

AND a."Code" NOT IN (
SELECT MAX (d."Code")
FROM tla_sg2org d
WHERE d."CodeOrg" = a."CodeOrg"
AND d."SGroup" = a."SGroup")
ORDER BY "CodeOrg", "SGroup"
DECLARE @Code Varchar(50)
OPEN TEST_CURSOR
FETCH NEXT FROM TEST_CURSOR
INTO @Code
WHILE @@FETCH_STATUS=0
BEGIN
BEGIN
delete from tla_sg2org where "Code" = @Code
IF(@@ROWCOUNT=0)
PRINT 'Failed to delete the row from the table'
END
FETCH NEXT FROM TEST_CURSOR
INTO @Code
END
CLOSE TEST_CURSOR
DEALLOCATE TEST_CURSOR

and i obtain this error

Msg 102, Level 15, State 1, Server ARUSFSRV37, Line 2
Incorrect syntax near 'Code'.
Msg 102, Level 15, State 1, Server ARUSFSRV37, Line 4
Incorrect syntax near 'CodeOrg'.
Msg 102, Level 15, State 1, Server ARUSFSRV37, Line 8
Incorrect syntax near 'SGroup'.
Msg 102, Level 15, State 1, Server ARUSFSRV37, Line 15
Incorrect syntax near 'Code'.

with bcp utility but with SSMS no. Any help?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-09-16 : 12:48:46


DELETE a
FROM tla_sg2org a
INNER JOIN (SELECT CodeOrg,SGroup
FROM tla_sg2org 
GROUP BY CodeOrg, SGroup
HAVING COUNT (1) > 1) b
AND b.SGroup = a.SGroup
AND b.CodeOrg = a.CodeOrg

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

rnavarro
Starting Member

10 Posts

Posted - 2012-09-17 : 07:51:47

Thanks for your prompt response
your query give me this error

Msg 156, Level 15, State 1, Line 4
Incorrect syntax near the keyword 'AND'.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-09-17 : 11:13:06

that was a typo


DELETE a
FROM tla_sg2org a
INNER JOIN (SELECT CodeOrg,SGroup
FROM tla_sg2org 
GROUP BY CodeOrg, SGroup
HAVING COUNT (1) > 1) b
AND ON b.SGroup = a.SGroup
AND b.CodeOrg = a.CodeOrg

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources