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]

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.PartN umber
FROM Inventory
WHERE (((Inventory.Pa rtNumber) In (Select Partnumber From Query2;)));


--
Jeffrey R. Bailey
"C L Humphreys" <clhumphreys@to ofgib.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]

[C L Humphreys]

Re: sql delete

"Jeffrey R. Bailey" <mrwizard1208@y ahoo.com> wrote in message
news:ymEbb.2507 5$Od.989918@twi ster.tampabay.r r.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.PartN umber
> FROM Inventory
> WHERE (((Inventory.Pa rtNumber) 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 (((personaldeta ils.studentrefn o) in (select studentrefno from
CLH-PersonalDetails DupMac2003;)));

I appreciate your help,
Chris

[Peter Doering]

Re: sql delete

Chris,
[color=blue]
> delete personaldetails .studentrefno
> from personaldetails
> where (((personaldeta ils.studentrefn o) in (select studentrefno from
> CLH-PersonalDetails DupMac2003;)));[/color]

Try this:

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

Mind the brackets.

HTH - Peter

--
No mails please.

[C L Humphreys]

Re: sql delete

"Peter Doering" <news@doering.o rg> wrote in message
news:bkn71r$3gg 52$1@ID-204768.news.uni-berlin.de...[color=blue]
> Chris,
>[color=green]
> > delete personaldetails .studentrefno
> > from personaldetails
> > where (((personaldeta ils.studentrefn o) in (select studentrefno from
> > CLH-PersonalDetails DupMac2003;)));[/color]
>
> Try this:
>
> DELETE FROM personaldetails AS P1
> WHERE P1.studentrefno In (SELECT T1.studentrefno
> FROM [CLH-PersonalDetails DupMac2003] 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