sql 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

General SQL Server Forums

New to SQL Server Programming

sql query

Author

Topic

Anand.A
Posting Yak Master

109 Posts

Posted - 2011-11-29 : 03:29:41

HI

i am having query like this in oracle when i try to execute this query in sql server i got error

so how to write this query in sql server

delete From EMP where [EMP_ID],[DEPT_ID) not in

(select [EMP_ID],MAX([DEPT_ID]) From EMP group by [EMP_ID])

anand

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-11-29 : 03:45:33

[code]
delete e From EMP e
left join (select [EMP_ID],MAX([DEPT_ID]) AS Dept_ID From EMP group by [EMP_ID])e1
on e1.[EMP_ID] =e.[EMP_ID]
and e1.Dept_ID = e.Dept_ID
where e1.[EMP_ID] is null
[/code]
EDIT: changed to reflect NOT IN condition
------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Anand.A
Posting Yak Master

109 Posts

Posted - 2011-11-29 : 03:49:47

ya thanks for ur reply

anand

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2011-11-29 : 05:42:04

quote:
Originally posted by visakh16

delete e From EMP e
inner join (select [EMP_ID],MAX([DEPT_ID]) AS Dept_ID From EMP group by [EMP_ID])e1
on e1.[EMP_ID] =e.[EMP_ID]
and e1.Dept_ID = e.Dept_ID

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

The OP specified NOT IN. So this will not work as expected

Madhivanan

Failing to plan is Planning to fail

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-11-29 : 06:54:35

quote:
Originally posted by madhivanan

quote:
Originally posted by visakh16

delete e From EMP e
inner join (select [EMP_ID],MAX([DEPT_ID]) AS Dept_ID From EMP group by [EMP_ID])e1
on e1.[EMP_ID] =e.[EMP_ID]
and e1.Dept_ID = e.Dept_ID

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

The OP specified NOT IN. So this will not work as expected

Madhivanan

Failing to plan is Planning to fail

Sorry thought it was IN. have edited the suggestion to cater to this now

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2011-12-01 : 05:19:52

quote:
Originally posted by visakh16

quote:
Originally posted by madhivanan

quote:
Originally posted by visakh16

delete e From EMP e
inner join (select [EMP_ID],MAX([DEPT_ID]) AS Dept_ID From EMP group by [EMP_ID])e1
on e1.[EMP_ID] =e.[EMP_ID]
and e1.Dept_ID = e.Dept_ID

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

The OP specified NOT IN. So this will not work as expected

Madhivanan

Failing to plan is Planning to fail

Sorry thought it was IN. have edited the suggestion to cater to this now

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Ok. No problem

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources