SQL Delete statement

[AlanAylett]

Hi all

wondering if anybody could spot anything wrong with this SQL DELETE
statement, both my Access and SQL skills are not what they used to be
so i reckon its something simple, any suggestions appreciated.

-----
DELETE dbo_SUMBill_New.*, dbo_SUMBill_New.BillAccountNumber,
dbo_SUMBill_New.POBReference

FROM a INNER JOIN dbo_SUMBill_New ON a.POBReference =
dbo_SUMBill_New.POBReference

WHERE (((dbo_SUMBill_New.BillAccountNumber)=[a].[BillAccountNumber])
AND ((dbo_SUMBill_New.POBReference)=[a].[POBReference]));
-----

Regards, Alan Aylett

[MGFoster]

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Might try these:

DELETE N.*
FROM dbo_SUMBill_New As N INNER JOIN A
ON N.POBReference = A.POBReference
AND N.BillAccountNumber = A.BillAccountNumber

or

DELETE *
FROM dbo_SUMBill_New As N
WHERE EXISTS (SELECT * FROM A
WHERE N.POBReference = A.POBReference
AND N.BillAccountNumber = A.BillAccountNumber)

As a safety precaution change DELETE to SELECT to test - it should show
you the records that will be selected for deletion.

These queries should delete the records from dbo_SUMBill_New that have
the same POBReference and BillAccountNumber as table A. Is this what
you want?

--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBQQ6BWoechKqOuFEgEQJd+gCghLYEGRsn/8WAa0i1pIQ3J19HCAMAnjPc
+/o8GqH0MdK1H27UEcqgYMwM
=/7+5
-----END PGP SIGNATURE-----
AlanAylett wrote:

Hi all

wondering if anybody could spot anything wrong with this SQL DELETE
statement, both my Access and SQL skills are not what they used to be
so i reckon its something simple, any suggestions appreciated.

-----
DELETE dbo_SUMBill_New.*, dbo_SUMBill_New.BillAccountNumber,
dbo_SUMBill_New.POBReference

FROM a INNER JOIN dbo_SUMBill_New ON a.POBReference =
dbo_SUMBill_New.POBReference

WHERE (((dbo_SUMBill_New.BillAccountNumber)=[a].[BillAccountNumber])
AND ((dbo_SUMBill_New.POBReference)=[a].[POBReference]));
-----

Regards, Alan Aylett

[AlanAylett]

Cheers for reply, unfortunately still experiencing problems.

probably didnt give enough information yesterday, brain was shot from
long office day.

my problem is more with access than the SQL, i am trying to run the
SQL in microsoft access 2000. If i replace the DELETE with SELECT it
returns all the records which i wish to delete, thus SQL works ok

However, when i try to execute the DELETE statement i get the error
message

'Could not delete from specified tables'

in a pop up box. The help supplied for this error is the usual ms
waffle which doesnt give you alot to work with.

Any access experts, please have a stab in the dark.

Regards, Alan Aylett

MGFoster <[e-mail]> wrote in message news:<Fj****************@newsread2.news.pas.earthl ink.net>...

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Might try these:

DELETE N.*
FROM dbo_SUMBill_New As N INNER JOIN A
ON N.POBReference = A.POBReference
AND N.BillAccountNumber = A.BillAccountNumber

or

DELETE *
FROM dbo_SUMBill_New As N
WHERE EXISTS (SELECT * FROM A
WHERE N.POBReference = A.POBReference
AND N.BillAccountNumber = A.BillAccountNumber)

As a safety precaution change DELETE to SELECT to test - it should show
you the records that will be selected for deletion.

These queries should delete the records from dbo_SUMBill_New that have
the same POBReference and BillAccountNumber as table A. Is this what
you want?

--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBQQ6BWoechKqOuFEgEQJd+gCghLYEGRsn/8WAa0i1pIQ3J19HCAMAnjPc
+/o8GqH0MdK1H27UEcqgYMwM
=/7+5
-----END PGP SIGNATURE-----
AlanAylett wrote:

Hi all

wondering if anybody could spot anything wrong with this SQL DELETE
statement, both my Access and SQL skills are not what they used to be
so i reckon its something simple, any suggestions appreciated.

-----
DELETE dbo_SUMBill_New.*, dbo_SUMBill_New.BillAccountNumber,
dbo_SUMBill_New.POBReference

FROM a INNER JOIN dbo_SUMBill_New ON a.POBReference =
dbo_SUMBill_New.POBReference

WHERE (((dbo_SUMBill_New.BillAccountNumber)=[a].[BillAccountNumber])
AND ((dbo_SUMBill_New.POBReference)=[a].[POBReference]));
-----

Regards, Alan Aylett

[MGFoster]

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Just a guess: Do you have permission to delete on the MS SQL table?
Check the Permissions on the table you are trying to delete on. Use the
SQL Enterprise Manager.

--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBQQ/TFoechKqOuFEgEQIZLACfeNl0Xb7tK1ajh8VY/ZubBVqskj8AnjW+
3kJBUfcY3r+UflJsQO6GwfF4
=PF+N
-----END PGP SIGNATURE-----
AlanAylett wrote:

Cheers for reply, unfortunately still experiencing problems.

probably didnt give enough information yesterday, brain was shot from
long office day.

my problem is more with access than the SQL, i am trying to run the
SQL in microsoft access 2000. If i replace the DELETE with SELECT it
returns all the records which i wish to delete, thus SQL works ok

However, when i try to execute the DELETE statement i get the error
message

'Could not delete from specified tables'

in a pop up box. The help supplied for this error is the usual ms
waffle which doesnt give you alot to work with.

Any access experts, please have a stab in the dark.

Regards, Alan Aylett

MGFoster <[e-mail]> wrote in message news:<Fj****************@newsread2.news.pas.earthl ink.net>...

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Might try these:

DELETE N.*
FROM dbo_SUMBill_New As N INNER JOIN A
ON N.POBReference = A.POBReference
AND N.BillAccountNumber = A.BillAccountNumber

or

DELETE *
FROM dbo_SUMBill_New As N
WHERE EXISTS (SELECT * FROM A
WHERE N.POBReference = A.POBReference
AND N.BillAccountNumber = A.BillAccountNumber)

As a safety precaution change DELETE to SELECT to test - it should show
you the records that will be selected for deletion.

These queries should delete the records from dbo_SUMBill_New that have
the same POBReference and BillAccountNumber as table A. Is this what
you want?

--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

[Nicole]

> my problem is more with access than the SQL, i am trying to run the

SQL in microsoft access 2000. If i replace the DELETE with SELECT it
returns all the records which i wish to delete, thus SQL works ok

However, when i try to execute the DELETE statement i get the error
message

'Could not delete from specified tables'

in a pop up box. The help supplied for this error is the usual ms
waffle which doesnt give you alot to work with.

Do you have any relationships with delete contraints that might be interfering?