By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
425,863 Members | 879 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 425,863 IT Pros & Developers. It's quick & easy.

sql delete

P: n/a
Hi,

Should I be able to do the following delete in access 2002 -

'delete from table1 where table1.x in (select x from table2)'

I get a syntax error. I have also tried 'delete * from...'

Anyone help?
cheers,
Chris


Nov 12 '05 #1
Share this Question
Share on Google+
4 Replies


P: n/a
Try adding a semi-colon to the end of the select statement in parentheses,
also you should include the field in the delete statement, it will delete
the entire row anyway. Here is some sample SQL doing a similar thing only
based on a query instead of a table(makes no difference).

DELETE Inventory.PartNumber
FROM Inventory
WHERE (((Inventory.PartNumber) In (Select Partnumber From Query2;)));
--
Jeffrey R. Bailey
"C L Humphreys" <cl*********@toofgib.moc> wrote in message
news:bk**********@ucsnew1.ncl.ac.uk...
Hi,

Should I be able to do the following delete in access 2002 -

'delete from table1 where table1.x in (select x from table2)'

I get a syntax error. I have also tried 'delete * from...'

Anyone help?
cheers,
Chris


Nov 12 '05 #2

P: n/a
"Jeffrey R. Bailey" <mr**********@yahoo.com> wrote in message
news:ym*******************@twister.tampabay.rr.com ...
Try adding a semi-colon to the end of the select statement in parentheses,
also you should include the field in the delete statement, it will delete
the entire row anyway. Here is some sample SQL doing a similar thing only
based on a query instead of a table(makes no difference).

DELETE Inventory.PartNumber
FROM Inventory
WHERE (((Inventory.PartNumber) In (Select Partnumber From Query2;)));


I must be missing something simple here.. It still returns with the syntax
error (displaying the 2nd part of the statement - after 'where').

This is my SQL:

delete personaldetails.studentrefno
from personaldetails
where (((personaldetails.studentrefno) in (select studentrefno from
CLH-PersonalDetailsDupMac2003;)));

I appreciate your help,
Chris
Nov 12 '05 #3

P: n/a
Chris,
delete personaldetails.studentrefno
from personaldetails
where (((personaldetails.studentrefno) in (select studentrefno from
CLH-PersonalDetailsDupMac2003;)));


Try this:

DELETE FROM personaldetails AS P1
WHERE P1.studentrefno In (SELECT T1.studentrefno
FROM [CLH-PersonalDetailsDupMac2003] AS T1
WHERE T1.studentrefno = P1.studentrefno);

Mind the brackets.

HTH - Peter

--
No mails please.
Nov 12 '05 #4

P: n/a
"Peter Doering" <ne**@doering.org> wrote in message
news:bk************@ID-204768.news.uni-berlin.de...
Chris,
delete personaldetails.studentrefno
from personaldetails
where (((personaldetails.studentrefno) in (select studentrefno from
CLH-PersonalDetailsDupMac2003;)));


Try this:

DELETE FROM personaldetails AS P1
WHERE P1.studentrefno In (SELECT T1.studentrefno
FROM [CLH-PersonalDetailsDupMac2003] AS T1
WHERE T1.studentrefno = P1.studentrefno);


Great, works perfectly.
I'm thinking it was down to the clh-personaldetails... being in square
brackets, I guess access doesn't like non-alphanumeric table names.

Thanks,
Chris
Nov 12 '05 #5

This discussion thread is closed

Replies have been disabled for this discussion.