Problems deleteing - 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

General SQL Server Forums

New to SQL Server Programming

Problems deleteing

Author

Topic

GavinD1977
Yak Posting Veteran

83 Posts

Posted - 2006-08-09 : 04:01:53

Hello all.

I am using the following statement to establish which rows have duplicate information using the following statement:

SELECT EMPLOY_REF, SAL_DATE FROM @TEMP_TABLE GROUP BY EMPLOY_REF, SAL_DATE HAVING COUNT(*) > 1

Once i have established these rows I want to delete them from the table. The code i had thought about (but QA doesnt like it) was:

DELETE FROM @TEMP_TABLE
WHERE EMPLOY_REF AND SAL_DATE IN (SELECT EMPLOY_REF, SAL_DATE FROM @TEMP_TABLE GROUP BY EMPLOY_REF, SAL_DATE HAVING COUNT(*) > 1)

Can anyone suggest a way to go about this? Thanks people :)

chiragkhabaria
Master Smack Fu Yak Hacker

1907 Posts

Posted - 2006-08-09 : 04:12:22

you can not use multiple columns in the sub query


DELETE  T FROM @TEMP_TABLE T Inner Join 
(SELECT  EMPLOY_REF,SAL_DATE FROM @TEMP_TABLE GROUP BY EMPLOY_REF, SAL_DATE HAVING COUNT(*) > 1) as F On f.Sal_Date = T.Sal_Date And 
F.Employ_Ref = T.Employ_Ref 

OR 

DELETE  T FROM @TEMP_TABLE as T
WHERE SAL_DATE IN (SELECT EMPLOY_REF, SAL_DATE FROM @TEMP_TABLE T1
Where T1.EMPLOY_REF = T.EMPLOY_REF GROUP BY EMPLOY_REF, SAL_DATE HAVING COUNT(*) > 1)

Chirag

GavinD1977
Yak Posting Veteran

83 Posts

Posted - 2006-08-09 : 04:16:31

Hi, thanks for the reply. I tried your second suggestion (looked easier for me to ubderstand), but got the following error:

Server: Msg 116, Level 16, State 1, Line 36
Only one expression can be specified in the select list when the subquery is not introduced with EXISTS.

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2006-08-09 : 04:20:13

quote:
Originally posted by GavinD1977

Hi, thanks for the reply. I tried your second suggestion (looked easier for me to ubderstand), but got the following error:

Server: Msg 116, Level 16, State 1, Line 36
Only one expression can be specified in the select list when the subquery is not introduced with EXISTS.

I think Chirag had a typo in the query. It should be:

DELETE T FROM @TEMP_TABLE as T
WHERE SAL_DATE IN (SELECT SAL_DATE FROM @TEMP_TABLE T1
Where T1.EMPLOY_REF = T.EMPLOY_REF GROUP BY EMPLOY_REF, SAL_DATE HAVING COUNT(*) > 1)

Harsh Athalye
India.
"Nothing is Impossible"

GavinD1977
Yak Posting Veteran

83 Posts

Posted - 2006-08-09 : 04:22:32

Hi, thanks. That seems to work now.

I know this is being a pain. But could someone maybe explain a little what is actually going on here? I'm a little confused by the code.

Thanks all.

chiragkhabaria
Master Smack Fu Yak Hacker

1907 Posts

Posted - 2006-08-09 : 04:23:45

opps copy paste mistake

Try this


DELETE  T FROM @TEMP_TABLE as T
WHERE SAL_DATE IN (SELECT  SAL_DATE FROM @TEMP_TABLE T1
Where T1.EMPLOY_REF = T.EMPLOY_REF GROUP BY EMPLOY_REF, SAL_DATE HAVING COUNT(*) > 1)

Chirag

GavinD1977
Yak Posting Veteran

83 Posts

Posted - 2006-08-09 : 04:30:18

Hi, yeah that code now works no problem. I'm just a little confused as to how it all works :-s

Thanks people.

chiragkhabaria
Master Smack Fu Yak Hacker

1907 Posts

Posted - 2006-08-09 : 04:34:37

quote:

I know this is being a pain. But could someone maybe explain a little what is actually going on here? I'm a little confused by the code.

Since you cannot compare the 2 Columns simulataneously in the sub query in SQL Server 2000, i guess feature exists with oracle,
so we are just comporing one column within the sub query and one outside the subquery.

hope this makes any sense to you..

quote:

I think Chirag had a typo in the query

Thanks Harsh for your input

Chirag

GavinD1977
Yak Posting Veteran

83 Posts

Posted - 2006-08-09 : 04:38:08

Ahhh, OK. Reading that and then looking over the code now makes more sense.

Thank you very much guys :)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources