sql delete

[C L Humphreys]

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

[Jeffrey R. Bailey]

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

[C L Humphreys]

"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

[Peter Doering]

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.

[C L Humphreys]

"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