What is wrong with this Delete Query?

[Guest]

Hello:

For the life of me, I cannot figure out what's wrong with the following
delete query:

DELETE
JJPRD
FROM
JJPRD
INNER JOIN DUPLICATE AS B
ON JJPRD.AERNo = B.AERNo AND JJPRD.VersionNo = B.VersionNo
WHERE
JJPRD.RecUnit is Null

When I try to execute this query, I get an error message, "Specify the table
that contains the records you want to delete"

Please!

Venkat

 

[LeAnne]

Hello:

For the life of me, I cannot figure out what's wrong with the following
delete query:

DELETE
JJPRD
FROM
JJPRD
INNER JOIN DUPLICATE AS B
ON JJPRD.AERNo = B.AERNo AND JJPRD.VersionNo = B.VersionNo
WHERE
JJPRD.RecUnit is Null

When I try to execute this query, I get an error message, "Specify the table
that contains the records you want to delete"

Please!

Venkat

Hi Venkat,

Which fields are you trying to delete from the table JJPRD? You didn't
specify in the DELETE clause. To delete entire records from JJPRD where
JJPRD.AERNo equals B.AERNo AND JJPRD.VersionNo equals B.VersionNo, and
where the WHERE condition is true, use the * :

DELETE [JJPRD].*
FROM [JJPRD] INNER JOIN [DUPLICATE] AS B ON [JJPRD].AERNo = B.AERNo AND
[JJPRD].VersionNo = B.VersionNo
WHERE [JJPRD].RecUnit is Null;

....Or you can list the fieldnames.

Is this what you're trying to accomplish?

hth,

LeAnne

 

[Guest]

LeAnne:

Yes, I am trying to delete the entire row. (On a different note, how can
you delete select columns? But this is for another day!)

Here's my SQL:

DELETE [JJPRD].*
FROM [JJPRD] INNER JOIN [DUPLICATE] AS B ON [JJPRD].AERNo=B.AERNo AND
[JJPRD].VersionNo=B.VersionNo
WHERE [JJPRD].RecUnit is Null;

I get an error message, "Could not delete from specified tables"

At least, it is able to figure out what table it is trying to delete rows
from. That's progress to me!

Thanks for your help.

Venkat

Hello:

For the life of me, I cannot figure out what's wrong with the following
delete query:

DELETE
JJPRD
FROM
JJPRD
INNER JOIN DUPLICATE AS B
ON JJPRD.AERNo = B.AERNo AND JJPRD.VersionNo = B.VersionNo
WHERE
JJPRD.RecUnit is Null

When I try to execute this query, I get an error message, "Specify the table
that contains the records you want to delete"

Please!

Venkat

Hi Venkat,

Which fields are you trying to delete from the table JJPRD? You didn't
specify in the DELETE clause. To delete entire records from JJPRD where
JJPRD.AERNo equals B.AERNo AND JJPRD.VersionNo equals B.VersionNo, and
where the WHERE condition is true, use the * :

DELETE [JJPRD].*
FROM [JJPRD] INNER JOIN [DUPLICATE] AS B ON [JJPRD].AERNo = B.AERNo AND
[JJPRD].VersionNo = B.VersionNo
WHERE [JJPRD].RecUnit is Null;

....Or you can list the fieldnames.

Is this what you're trying to accomplish?

hth,

LeAnne

 

[Guest]

Leanne:

Yes, I am trying to delete the entire row. (On a different note, how can
you delete select columns? But this is for another day!)

Here's my SQL:

DELETE [JJPRD].*
FROM [JJPRD] INNER JOIN [DUPLICATE] AS B ON [JJPRD].AERNo=B.AERNo AND
[JJPRD].VersionNo=B.VersionNo
WHERE [JJPRD].RecUnit is Null;

I get an error message, "Could not delete from specified tables"

At least, it is able to figure out what table it is trying to delete rows
from. That's progress to me!

Thanks for your help.

Venkat

Hello:

For the life of me, I cannot figure out what's wrong with the following
delete query:

DELETE
JJPRD
FROM
JJPRD
INNER JOIN DUPLICATE AS B
ON JJPRD.AERNo = B.AERNo AND JJPRD.VersionNo = B.VersionNo
WHERE
JJPRD.RecUnit is Null

When I try to execute this query, I get an error message, "Specify the table
that contains the records you want to delete"

Please!

Venkat

Hi Venkat,

Which fields are you trying to delete from the table JJPRD? You didn't
specify in the DELETE clause. To delete entire records from JJPRD where
JJPRD.AERNo equals B.AERNo AND JJPRD.VersionNo equals B.VersionNo, and
where the WHERE condition is true, use the * :

DELETE [JJPRD].*
FROM [JJPRD] INNER JOIN [DUPLICATE] AS B ON [JJPRD].AERNo = B.AERNo AND
[JJPRD].VersionNo = B.VersionNo
WHERE [JJPRD].RecUnit is Null;

....Or you can list the fieldnames.

Is this what you're trying to accomplish?

hth,

LeAnne

 

[Guest]

LeAnne:

Yes, I am trying to delete the entire row. (On a different note, how can
you delete select columns? But this is for another day!)

Here's my SQL:

DELETE [JJPRD].*
FROM [JJPRD] INNER JOIN [DUPLICATE] AS B ON [JJPRD].AERNo=B.AERNo AND
[JJPRD].VersionNo=B.VersionNo
WHERE [JJPRD].RecUnit is Null;

I get an error message, "Could not delete from specified tables"

At least, it is able to figure out what table it is trying to delete rows
from. That's progress to me!

Thanks for your help.

Venkat

Hello:

For the life of me, I cannot figure out what's wrong with the following
delete query:

DELETE
JJPRD
FROM
JJPRD
INNER JOIN DUPLICATE AS B
ON JJPRD.AERNo = B.AERNo AND JJPRD.VersionNo = B.VersionNo
WHERE
JJPRD.RecUnit is Null

When I try to execute this query, I get an error message, "Specify the table
that contains the records you want to delete"

Please!

Venkat

Hi Venkat,

Which fields are you trying to delete from the table JJPRD? You didn't
specify in the DELETE clause. To delete entire records from JJPRD where
JJPRD.AERNo equals B.AERNo AND JJPRD.VersionNo equals B.VersionNo, and
where the WHERE condition is true, use the * :

DELETE [JJPRD].*
FROM [JJPRD] INNER JOIN [DUPLICATE] AS B ON [JJPRD].AERNo = B.AERNo AND
[JJPRD].VersionNo = B.VersionNo
WHERE [JJPRD].RecUnit is Null;

....Or you can list the fieldnames.

Is this what you're trying to accomplish?

hth,

LeAnne

 

[Guest]

LeAnne:

Yes, I am trying to delete the entire row. (On a different note, how can
you delete select columns? But this is for another day!)

Here's my SQL:

DELETE [JJPRD].*
FROM [JJPRD] INNER JOIN [DUPLICATE] AS B ON [JJPRD].AERNo=B.AERNo AND
[JJPRD].VersionNo=B.VersionNo
WHERE [JJPRD].RecUnit is Null;

I get an error message, "Could not delete from specified tables"

At least, it is able to figure out what table it is trying to delete rows
from. That's progress to me!

Thanks for your help.

Venkat

Hello:

For the life of me, I cannot figure out what's wrong with the following
delete query:

DELETE
JJPRD
FROM
JJPRD
INNER JOIN DUPLICATE AS B
ON JJPRD.AERNo = B.AERNo AND JJPRD.VersionNo = B.VersionNo
WHERE
JJPRD.RecUnit is Null

When I try to execute this query, I get an error message, "Specify the table
that contains the records you want to delete"

Please!

Venkat

Hi Venkat,

Which fields are you trying to delete from the table JJPRD? You didn't
specify in the DELETE clause. To delete entire records from JJPRD where
JJPRD.AERNo equals B.AERNo AND JJPRD.VersionNo equals B.VersionNo, and
where the WHERE condition is true, use the * :

DELETE [JJPRD].*
FROM [JJPRD] INNER JOIN [DUPLICATE] AS B ON [JJPRD].AERNo = B.AERNo AND
[JJPRD].VersionNo = B.VersionNo
WHERE [JJPRD].RecUnit is Null;

....Or you can list the fieldnames.

Is this what you're trying to accomplish?

hth,

LeAnne

 

[John Vinson]

When I try to execute this query, I get an error message, "Specify the table
that contains the records you want to delete"

You're just an asterisk away:

DELETE
JJPRD.*
FROM
JJPRD
INNER JOIN DUPLICATE AS B
ON JJPRD.AERNo = B.AERNo AND JJPRD.VersionNo = B.VersionNo
WHERE
JJPRD.RecUnit is Null


John W. Vinson[MVP]
Join the online Access Chats
Tuesday 11am EDT - Thursday 3:30pm EDT
http://community.compuserve.com/msdevapps

 

[Guest]

John:

As I have indicated in my previous message, now Access says it;s unable to
delete the rows in the table. Even after I opended the database in exclusive
mode!

I gave up on Access and did everything in SQL Server and exported the result
table in Access.

Thanks.

Venkat

 

[Guest]

I'm not sure if you mentioned the Access version, but I believe that in
Access 2000 and later you may need to set the unique records property to
"yes", which will insert "DISTINCTROW" after the word "DELETE" in the sql. I
don't think this is an issue with single table delete queries, but I believe
it is encountered when you have joins to other tables in the queries.

 

[John Spencer (MVP)]

Try the following

DELETE DISTINCTROW [JJPRD].*
FROM [JJPRD] INNER JOIN [DUPLICATE] AS B
ON [JJPRD].AERNo=B.AERNo AND [JJPRD].VersionNo=B.VersionNo
WHERE [JJPRD].RecUnit is Null;

If that doesn't work, then you can try using a subqueries. MAKE A COPY and TEST
this first.

DELETE DISTINCTROW [J].*
FROM [JJPRD] As J
WHERE [J].AERNo IN (SELECT B.AerNo FROM Duplicate as B) AND
AND J.VersionNo IN (SELECT D.VersionNo FROM Duplicate as D)
AND [J].RecUnit is Null;

LeAnne:

Yes, I am trying to delete the entire row. (On a different note, how can
you delete select columns? But this is for another day!)

Here's my SQL:

DELETE [JJPRD].*
FROM [JJPRD] INNER JOIN [DUPLICATE] AS B ON [JJPRD].AERNo=B.AERNo AND
[JJPRD].VersionNo=B.VersionNo
WHERE [JJPRD].RecUnit is Null;

I get an error message, "Could not delete from specified tables"

At least, it is able to figure out what table it is trying to delete rows
from. That's progress to me!

Thanks for your help.

Venkat

Hello:

For the life of me, I cannot figure out what's wrong with the following
delete query:

DELETE
JJPRD
FROM
JJPRD
INNER JOIN DUPLICATE AS B
ON JJPRD.AERNo = B.AERNo AND JJPRD.VersionNo = B.VersionNo
WHERE
JJPRD.RecUnit is Null

When I try to execute this query, I get an error message, "Specify the table
that contains the records you want to delete"

Please!

Venkat

Hi Venkat,

Which fields are you trying to delete from the table JJPRD? You didn't
specify in the DELETE clause. To delete entire records from JJPRD where
JJPRD.AERNo equals B.AERNo AND JJPRD.VersionNo equals B.VersionNo, and
where the WHERE condition is true, use the * :

DELETE [JJPRD].*
FROM [JJPRD] INNER JOIN [DUPLICATE] AS B ON [JJPRD].AERNo = B.AERNo AND
[JJPRD].VersionNo = B.VersionNo
WHERE [JJPRD].RecUnit is Null;

....Or you can list the fieldnames.

Is this what you're trying to accomplish?

hth,

LeAnne