left only 1 record... delete the others

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

General SQL Server Forums

New to SQL Server Programming

left only 1 record... delete the others

Author

Topic

cwtriguns2002
Constraint Violating Yak Guru

272 Posts

Posted - 2008-01-22 : 04:05:48

Hi everyone... I have a problem on deleting a record. I accidentally duplicate the record.
I need to delete other records and left only 1 record based on each date and employeenumber.

supposing i have a table hrempshiftschedule
i have 3 fields... empno, shiftdate, and shifttype

sample data...
empno shiftdate shifttype
1 1/1/2008 S1
1 1/1/2008 S1
2 1/1/2008 S2
2 1/1/2008 S2
2 1/1/2008 S2
2 1/2/2008 S2
2 1/2/2008 S2
2 1/3/2008 S3
3 1/1/2008 S3
4 1/4/2008 S4
4 1/5/2008 S5

expected output....

empno shiftdate shifttype
1 1/1/2008 S1
2 1/1/2008 S2
2 1/2/2008 S2
2 1/3/2008 S3
3 1/1/2008 S3
4 1/4/2008 S4
4 1/5/2008 S5

pls help and guide me.... thanks

RON
________________________________________________________________________________________________
"I won't last a day without SQL"

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-01-22 : 04:16:11

Use this:-

declare @temp table
(empno int,
shiftdate datetime,
shifttype char(2)
)

INSERT INTO @temp VALUES(1 ,'1/1/2008', 'S1')
INSERT INTO @temp VALUES(1 ,'1/1/2008', 'S1')
INSERT INTO @temp VALUES(2 ,'1/1/2008', 'S2')
INSERT INTO @temp VALUES(2 ,'1/1/2008', 'S2')
INSERT INTO @temp VALUES(2 ,'1/1/2008', 'S2')
INSERT INTO @temp VALUES(2 ,'1/2/2008', 'S2')
INSERT INTO @temp VALUES(2 ,'1/2/2008', 'S2')
INSERT INTO @temp VALUES(2 ,'1/3/2008', 'S3')
INSERT INTO @temp VALUES(3 ,'1/1/2008', 'S3')
INSERT INTO @temp VALUES(4 ,'1/4/2008', 'S4')
INSERT INTO @temp VALUES(4 ,'1/5/2008', 'S5')
;
With Your_CTE AS
(
SELECT ROW_NUMBER() OVER (PARTITION BY empno,shiftdate,shifttype ORDER BY shiftdate) AS RowNo,
empno,
shiftdate,
shifttype
FROM @temp
)

DELETE FROM Your_CTE where RowNo<>1

select * from @temp


output
--------

empno       shiftdate               shifttype
----------- ----------------------- ---------
1           2008-01-01 00:00:00.000 S1
2           2008-01-01 00:00:00.000 S2
2           2008-01-02 00:00:00.000 S2
2           2008-01-03 00:00:00.000 S3
3           2008-01-01 00:00:00.000 S3
4           2008-01-04 00:00:00.000 S4
4           2008-01-05 00:00:00.000 S5

ditch
Master Smack Fu Yak Hacker

1466 Posts

Posted - 2008-01-22 : 04:17:04

select distinct empno, shiftdate, shifttype into mytemptable
from hrempshiftschedule

--*****************
once you have done that - confirm that the data in mytemptable is exactly what you want, then drop the hrempshiftschedule table and rename mytemptable to hrempshiftschedule, that should work fine if there aren't any foreign key constraints - if there are then you need to first disable the constraints then drop, rename and reenable the constraints.

Duane.

cwtriguns2002
Constraint Violating Yak Guru

272 Posts

Posted - 2008-01-22 : 04:18:52

so quick... i will test it right now...
ok thanks...

RON
________________________________________________________________________________________________
"I won't last a day without SQL"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-01-22 : 04:32:02

Why the use of CTE?

-- Prepare sample data
DECLARE @Temp TABLE (EmpNo INT, ShiftDate DATETIME, ShiftType CHAR(2))

INSERT	@Temp
SELECT	1, '1/1/2008', 'S1' UNION ALL
SELECT	1, '1/1/2008', 'S1' UNION ALL
SELECT	2, '1/1/2008', 'S2' UNION ALL
SELECT	2, '1/1/2008', 'S2' UNION ALL
SELECT	2, '1/1/2008', 'S2' UNION ALL
SELECT	2, '1/2/2008', 'S2' UNION ALL
SELECT	2, '1/2/2008', 'S2' UNION ALL
SELECT	2, '1/3/2008', 'S3' UNION ALL
SELECT	3, '1/1/2008', 'S3' UNION ALL
SELECT	4, '1/4/2008', 'S4' UNION ALL
SELECT	4, '1/5/2008', 'S5'

-- Show original data
SELECT	*
FROM	@Temp

-- Delete duplicates
DELETE	y
FROM	(
		SELECT	ROW_NUMBER() OVER (PARTITION BY EmpNo, ShiftDate, ShiftType ORDER BY ShiftDate) AS RecID
		FROM	@Temp
	) AS y
WHERE	RecID > 1

-- Show cleaned data
SELECT	*
FROM	@Temp

E 12°55'05.25"
N 56°04'39.16"

cwtriguns2002
Constraint Violating Yak Guru

272 Posts

Posted - 2008-01-22 : 04:42:32

thanks to both of you... i appreciate it...
thanks

RON
________________________________________________________________________________________________
"I won't last a day without SQL"

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-01-22 : 06:26:09

See what you do with row_number()
http://sqlblogcasts.com/blogs/madhivanan/archive/2007/08/27/multipurpose-row-number-function.aspx

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources