sql delete

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

re: sql delete

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" <clhumphreys@toofgib.moc> wrote in message
news:bkn1o3$f6a$1@ucsnew1.ncl.ac.uk...[color=blue]
> 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
>
>
>
>
>[/color]

re: sql delete

"Jeffrey R. Bailey" <mrwizard1208@yahoo.com> wrote in message
news:ymEbb.25075$Od.989918@twister.tampabay.rr.com ...[color=blue]
> 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;)));[/color]

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

re: sql delete

Chris,
[color=blue]
> delete personaldetails.studentrefno
> from personaldetails
> where (((personaldetails.studentrefno) in (select studentrefno from
> CLH-PersonalDetailsDupMac2003;)));[/color]

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.

re: sql delete

"Peter Doering" <news@doering.org> wrote in message
news:bkn71r$3gg52$1@ID-204768.news.uni-berlin.de...[color=blue]
> Chris,
>[color=green]
> > delete personaldetails.studentrefno
> > from personaldetails
> > where (((personaldetails.studentrefno) in (select studentrefno from
> > CLH-PersonalDetailsDupMac2003;)));[/color]
>
> Try this:
>
> DELETE FROM personaldetails AS P1
> WHERE P1.studentrefno In (SELECT T1.studentrefno
> FROM [CLH-PersonalDetailsDupMac2003] AS T1
> WHERE T1.studentrefno = P1.studentrefno);[/color]

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