Converting my SELECT into a DELETE

Question

Converting my SELECT into a DELETE

Carl-207103

SSC Enthusiast

Points: 112
More actions
November 17, 2006 at 6:25 am

#153303

Hi
I'm sure I'm missing something obvious, however, here is my SELECT which I've used to identify the records for deletion. Why can I not swap the SELECT x, y, z FROm for a DELETE FROM?

select * from nsturesults n inner join _temp t on n.setid = t.setid and n.groupid = t.groupid and n.studentid = t.stuid where n.setid = '2006-07' and n.deptid = 'ma' and n.examid ='6thformnov'

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

ijaz SSCommitted Points: 1912 More actions · Answer 1

can you give the syntax for your delete you are going to try.

DELETE FROM nsturesults n inner join _temp t on n.setid = t.setid and n.groupid = t.groupid and n.studentid = t.stuid where n.setid = '2006-07' and n.deptid = 'ma' and n.examid ='6thformnov'

Try the above , if i get correct which you are going to delete ...

cheers

Carl-207103 SSC Enthusiast Points: 112 More actions · Answer 2

This is it:

delete from nsturesults n inner join _temp t on n.setid = t.setid and n.groupid = t.groupid and n.studentid = t.stuid where n.setid = '2006-07' and n.deptid = 'ma' and n.examid ='6thformnov'

Get error Incorrect Syntax near 'n'

Without the table alias' i get syntax error near 'inner'

ijaz SSCommitted Points: 1912 More actions · Answer 3

Hi,

You should not specify the optional delete clause in this senario ... so change your query and hope it will work now

delete nsturesults from nsturesults n inner join _temp t on n.setid = t.setid and n.groupid = t.groupid and n.studentid = t.stuid where n.setid = '2006-07' and n.deptid = 'ma' and n.examid ='6thformnov'

cheers

ijaz SSCommitted Points: 1912 More actions · Answer 4

Hi,

If you should get the error after this change, then replace the complete table name 'nsturesults' with the alias used in your query 'n'

Cheers

cheers

Carl-207103 SSC Enthusiast Points: 112 More actions · Answer 5

Thanks for your help, I finally chose to write a simple vba routine in access to cycle thru the _temp table and run a delete row by row against the results. - Did exactly what I wanted.

Cheers

ijaz SSCommitted Points: 1912 More actions · Answer 6

But I think regarding to performance you should adopt the stored procedure option and the query work much better than you are getting now.

any how, if this is not the matter then surely enjoy the solution you have done. otherwise the above. I have tried in the pub database with the following query and its work perfactly

DELETE titleauthor

FROM titleauthor INNER JOIN

titles ON titleauthor.title_id = titles.title_id

WHERE (titles.title LIKE '%Straight%')

cheers

Ken McKelvey SSCoach Points: 18843 More actions · Answer 7

or

DELETE FROM titleauthor

WHERE EXISTS (

SELECT *

FROM titles T

WHERE T.title LIKE '%Straight%'

AND T.title_id = titleauthor.title_id )

Johan Bijnens SSC Guru Points: 135369 More actions · Answer 8

just my 2ct...

- use aliasses ! IMO it makes it way easyer to read and may even reduce your chances for mistake. If you name your aliasses properly (wich I didn't in the example ! )

DELETE DelObj

FROM titleauthor DelObj INNER JOIN

titles ON DelObj.title_id = titles.title_id

WHERE (titles.title LIKE '%Straight%')

- Think of locking when performing this kind of deletes ! Lockescalation may occur if you hit more than x rows and no other users may be able to use the table during your deletes. You could use Set Rowcount and put it in a while loop that has a break when @@rowcount after delete is 0

- if your delete hit a reasonable amount of rows of your table, consider rebuilding the indexes so everything is nice and well ordered for further use.

Johan

Learn to play, play to learn !

Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:

- How to post Performance Problems
- How to post data/code to get the best help[/url]

- How to prevent a sore throat after hours of presenting ppt

press F1 for solution, press shift+F1 for urgent solution 😀

Need a bit of Powershell? How about this

Who am I ? Sometimes this is me but most of the time this is me