December 1, 2005 at 3:14 am
Hi,
I have a table with two columns.The structure is as shown below :
Name City
A B
A B
A B
A B
I have no primary key. And I want to delete 3 rows out of 4.
How can i delete them.
PLs help.
Thanks.
December 1, 2005 at 4:21 am
You can't. You can only delete a record if you can identify it. (At least in sQL 2000. In SQL 2005 the DELETE TOP 3 ... might work.)
Your best bet would be to add an identity column to the table and use that to do the delete. You can always drop the identity column later if you don't want it.
Are you trying to clean duplicate data out of a table?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
December 1, 2005 at 6:49 am
If you just want to clean up the data, you can put this data into a temp table that has an identity column in it, then delete the data and then truncate the main table and port the data back...something like this:
CREATE TABLE NO_KEY (NAME VARCHAR(5), CITY VARCHAR(5))
GO
INSERT INTO NO_KEY VALUES ('A', 'B')
INSERT INTO NO_KEY VALUES ('A', 'B')
INSERT INTO NO_KEY VALUES ('A', 'B')
INSERT INTO NO_KEY VALUES ('A', 'B')
INSERT INTO NO_KEY VALUES ('X', 'Y')
INSERT INTO NO_KEY VALUES ('X', 'Y')
GO
SELECT IDENTITY(INT, 1, 1) AS ROW_NUM, * INTO #TEMP FROM NO_KEY
DELETE FROM #TEMP
WHERE EXISTS (SELECT 1 FROM #TEMP X
WHERE X.NAME = #TEMP.NAME
AND X.CITY = #TEMP.CITY
AND X.ROW_NUM < #TEMP.ROW_NUM)
DELETE NO_KEY
GO
INSERT INTO NO_KEY (NAME, CITY) SELECT NAME, CITY FROM #TEMP
GO
SELECT * FROM NO_KEY
--Output
NAME CITY
----- -----
A B
X Y
(2 row(s) affected)
And once the data has been cleaned up, proceed with adding a PK to this table - like the above post said, add a surrogate key using identity.
December 1, 2005 at 6:57 am
Gr8 !!!
Thank you for ur help.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply