March 19, 2009 at 9:11 am
err..i missed the other part of join eh..
this one works i suppose
delete x from
( select row_number() OVER (order by getdate()) as id,col1,col2 from #t1 ) x,
( select row_number() OVER (order by getdate()) as id,col1,col2 from #t1) Y
where x.col2 = y.col1 and y.col2=x.col1 and x.id > y.id
Regards,
Raj
March 21, 2009 at 2:23 am
Here's an sql solution to leave one row from a pair of 'opposites'.
create table #t1 (A varchar(10), B varchar(10))
insert into #t1(A,B) select 'john', 'sainz'
insert into #t1(A,B) select 'john','candy'
insert into #t1(A,B) select 'irfan','khan'
insert into #t1(A,B) select 'sainz', 'john'
insert into #t1(A,B) select 'candy','john'
insert into #t1(A,B) select 'travolta', 'carlos'
-- These are the rows left in the table after eliminating 1 row of
-- 2 rows of opposite values.
WITH TEMP AS (SELECT ROW_NUMBER()OVER(ORDER BY GETDATE()) AS ID,A,B
FROM #T1)
SELECT *
FROM TEMP
WHERE ID NOT IN
(
SELECT XID
FROM
(SELECT X.ID AS XID,Y.ID AS YID
FROM TEMP AS X INNER JOIN TEMP AS Y
ON X.A=Y.B AND X.B=Y.A AND X.ID>Y.ID) AS C
)
ORDER BY ID
ID A B
-------------------- ---------- ----------
1 john sainz
2 john candy
3 irfan khan
6 travolta carlos
-- Query to delete a row of opposites.
WITH TEMP AS (SELECT ROW_NUMBER()OVER(ORDER BY GETDATE()) AS ID,A,B
FROM #T1)
DELETE FROM E FROM #T1 AS E
WHERE
EXISTS
(
SELECT A,B
FROM
(SELECT A,B
FROM TEMP
WHERE ID IN
(
SELECT XID
FROM
(
SELECT X.ID AS XID,Y.ID AS YID
FROM TEMP AS X INNER JOIN TEMP AS Y
ON X.A=Y.B AND X.B=Y.A AND X.ID>Y.ID) AS C
)
) AS F
/* These are rows in F (from TEMP) that we want to delete from #T1
A B
---------- ----------
sainz john
candy john
*/
WHERE E.A=F.A AND E.B=F.B )
SELECT * FROM #T1
A B
---------- ----------
john sainz
john candy
irfan khan
travolta carlos
In general this situation should never occur in a table. I would handle this with a constraint as explained here:
http://beyondsql.blogspot.com/2008/07/opposite-constraint.html
March 23, 2009 at 11:58 pm
USE Northwind
GO
CREATE TABLE [Employee]
(
[ID] [int] NOT NULL ,
[Name] [nvarchar] (10) NOT NULL ,
[Salary] [money] NULL ,
)
GO
INSERT INTO Employee VALUES
(1, 'Dinesh', 1500)
(1, 'Dinesh', 25000)
(1, 'Dinesh', 15000)
SELECT * FROM Employee ORDER BY 1
/*******************************************************************/
/* Script for Deletion of Duplicate Record from the Table */
GO
DECLARE @id INT,
@name VARCHAR (50),
@cnt INT,
@salary NUMERIC
DECLARE GetAllRecords CURSOR LOCAL STATIC FOR
SELECT COUNT(1), ID, NAME, salary
FROM Employee WITH (NOLOCK)
GROUP BY ID, NAME, salary
HAVING COUNT(1) > 1
OPEN GetAllRecords
FETCH NEXT FROM GetAllRecords INTO @cnt, @id, @name, @salary
--Cursor to check with all other records
WHILE @@FETCH_STATUS = 0
BEGIN
SET @cnt = @cnt - 1
SET rowcount @cnt
-- Deleting the duplicate records. Observe that all fields are mentioned at the where condition
DELETE FROM Employee WHERE ID = @id AND NAME = @name AND salary = @salary
SET rowcount 0
FETCH NEXT FROM GetAllRecords INTO @cnt, @id, @name, @salary
END
CLOSE GetAllRecords
DEALLOCATE GetAllRecords
/*******************************************************************/
GO
DROP TABLE Employee
GO
March 24, 2009 at 12:36 am
The problem discussed on this topic is different from what you have explained.
And btw, Why you need to use a cursor when you can do this with a query 😉
Regards,
Raj
Viewing 4 posts - 16 through 18 (of 18 total)
You must be logged in to reply to this topic. Login to reply