March 6, 2016 at 4:42 am
Hi there,
I am new to sql server and i am practicing on SQL Express. I have created a table and in error I have duplicated rows. My first column is 'm_id', however, I have noticed to the left there appears to be a row count. In order to delete my duplicated rows can i refer to the row numbers i.e. 5-10 in order to delete these rows?
Any help would be greatly appreciated.
Kind regards,
James Elwell
March 6, 2016 at 4:58 am
mrjae (3/6/2016)
Hi there,I am new to sql server and i am practicing on SQL Express. I have created a table and in error I have duplicated rows. My first column is 'm_id', however, I have noticed to the left there appears to be a row count. In order to delete my duplicated rows can i refer to the row numbers i.e. 5-10 in order to delete these rows?
Any help would be greatly appreciated.
Kind regards,
James Elwell
are the rows complete duplicates across all columns?
You'll need to supply the table definition and some sample data for a complete answer to be provided
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
March 6, 2016 at 4:58 am
mrjae (3/6/2016)
Hi there,I am new to sql server and i am practicing on SQL Express. I have created a table and in error I have duplicated rows. My first column is 'm_id', however, I have noticed to the left there appears to be a row count. In order to delete my duplicated rows can i refer to the row numbers i.e. 5-10 in order to delete these rows?
Any help would be greatly appreciated.
Kind regards,
James Elwell
Quick suggestion, have a look at this article by Sioban Krzywicki.[/url]
😎
March 9, 2016 at 11:33 am
Hi.
Welcome to the forums and to SQL.
The short answer is no you can't. SQL returns sets of data which are essentialy unordered. The row number you are seeing in the results grid is NOT part of the dataset, just an element in the environment window.
It sounds like you have not contsructed your table properly. If you don't want duplicates, then set the field to be a UNIQUE key. If you want to create a column that is guaranteed to be unique and sequential then use the following when setting up your table
CREATE TABLE tableName
(
ID INT IDENTITY(1,1),
...rest of table definition
)
The IDENTITY will auto-populate (i.e. don't pass it in as a value during an insert) the two values it takes are initial seed value and increment. If you said IDENTITY(7,3) then you would get ID numbers 7, 10, 13, 16 etc...
If you need a row number in the data and there is no appropriate field in the table then you can use the ROW_NUMBER() OVER() construct which will inject a unique sequential row number into the results set, but there is no guarantee that the same number will be returned against the same row each time.
If you want to fix your data and the records are truly identical then use
SELECT DISTINCT * INTO #tmpMyTable FROM myTable
TRUNCATE myTable
INSERT INTO myTable (
) SELECT
FROM #tmpMyTable
March 9, 2016 at 12:01 pm
Here is another possible way to delete a duplicate row:
-- delete all duplicates but first in each set
WITH cte AS
(
SELECT a.*,
ROW_NUMBER() OVER(PARTITION BY col1, col2 ORDER BY col1, col2) dupenbr
FROM dbo.mytable a
)
DELETE FROM cte
WHERE dupenbr > 1
;
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply