Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 301
  • Last Modified:

rown number Delete


What is wrong with my delete

getting this error
Msg 102, Level 15, State 1, Line 1
Incorrect syntax near '('.
Msg 102, Level 15, State 1, Line 5
Incorrect syntax near 'sp'.

Delete from (
Select *,
Row_NUmber() over (partition by EmailAddress order by CreationTime Desc) rn 
from OpenClickThru where EmailAddress is Not NULL
) sp
Where sp.rn > 1 ;

Open in new window

0
Leo Torres
Asked:
Leo Torres
  • 2
  • 2
  • 2
2 Solutions
 
dqmqCommented:
Try this:


;with cte as
(
Select *, Row_NUmber() over (partition by EmailAddress order by CreationTime Desc) rn
from OpenClickThru)
delete from cte
where EmailAddress is Not NULL and rn >1
0
 
ThomasianCommented:
To correct the syntax of your query:
Delete sp
FROM (Select *,
			Row_NUmber() over (partition by EmailAddress order by CreationTime Desc) rn 
		from OpenClickThru where EmailAddress is Not NULL
	) sp
Where sp.rn > 1 

Open in new window

0
 
Leo TorresSQL DeveloperAuthor Commented:
dqmq: yours worked fine

But I am still curious because I would like to know why
Thomasian: did not work this was what I was tring to do there must be a way to do this with out CTE right?

Msg 156, Level 15, State 1, Line 2
Incorrect syntax near the keyword 'Select'.
Msg 102, Level 15, State 1, Line 5
Incorrect syntax near ')'.
0
Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

 
ThomasianCommented:
Did you run the query as is? Maybe you left out the word "FROM" since I get the same error without it.
0
 
Leo TorresSQL DeveloperAuthor Commented:
Ha that's what it was..

Thanks
0
 
dqmqCommented:
Original query not missing "FROM", missing "SP" before the "FROM". :>)
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

  • 2
  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now