Problem with Delete Query - SQL Server Forums

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

Transact-SQL (2000)

Problem with Delete Query

Author

Topic

dsburton
Starting Member

6 Posts

Posted - 2005-06-09 : 05:34:32

Hi,

I have a table "TempEmp" with the following sample data:
ID empID UpdDate
1001 111 06/06/2005
1002 111 06/06/2005
1003 111 06/08/2005

I want to keep the first and the last record for a
particular employee and delete all the 'n' records in between.

my query goes like....

DELETE FROM TempEmp WHERE EmpID = 111
AND (ID <> 1003 AND UpdDate <> '06/08/2005')
AND (ID <> 1001 AND UpdDate <> '06/06/2005')

It says "0 row(s) affected.

whereas, it should be deleting the 2nd record. If I remove the condition checking on dates, then it is deleting the 2nd record. But I need to have the condition on dates as well.

Please Help!

Thanks and regards,
Dorothy

mr_mist
Grunnio

1870 Posts

Posted - 2005-06-09 : 05:40:51

SELECT DISTINCT
a.ID, a.empID, a.UpdDate
FROM TempEmp
INNER JOIN
(SELECT empID, min(UpdDate) as mindate FROM TempEmp GROUP BY empID) b
ON a.empID = b.empID and a.upddate = b.mindate
INNER JOIN
(SELECT empID, max(UpdDate) as maxdate FROM TempEmp GROUP BY empID) c
ON a.empID = c.empID and a.upddate = c.maxdate

Will select you the rows you want to keep. You could either run that and select them into a table then delete your old one~~, or change that one to left outer joins, use the where clause WHERE b.empID is null and c.empid is null and run it as a delete.~~ You can only run it that way because some of your dates are the same.

-------
Moo. :)

dsburton
Starting Member

6 Posts

Posted - 2005-06-09 : 05:44:48

Thank you, I'll try it out

duyn0045
Starting Member

1 Post

Posted - 2005-06-09 : 08:31:08

Hey, I was looking for the solution for the same problem, THNX!

----- Fight, Win, Prevail! -----

dsburton
Starting Member

6 Posts

Posted - 2005-06-09 : 08:35:56

There is an other solution for that... which does not require a temp table to created... deleted.. etc...

The Delete query can be changed to ....

DELETE FROM
TempEmp
WHERE
EmpID = 111 AND
NOT(ID = 1003 AND UpdDate = '06/08/2005') AND
NOT(ID = 1001 AND UpdDate = '06/06/2005')

Hope this helps you!!!

Dorothy

mr_mist
Grunnio

1870 Posts

Posted - 2005-06-09 : 09:12:14

It might work to write the solution that way Dorothy, but then you are doing all the work for yourself.

That's fine and dandy if you've only got to exclude 2 lines, but for a more practical solution you're better off actually getting the SQL Server to figure out what lines to exclude.

-------
Moo. :)

dsburton
Starting Member

6 Posts

Posted - 2005-06-09 : 09:24:12

Well, I agree that your solution is the efficient way to solve that problem (professionally

).... and also in the long run.
I just gave a work around for the query.

Infact I learnt quite a few things from your solution!! Thanks!

Dorothy

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources