Subquery restrictions

K

Ken Ellis

I have a table 'Temp' with two columns(SerialNumber and ProductDescription)
and 20 rows. I am trying to use this table to delete any rows in a table
called 'Target' with SerialNumbers that appear in 'Temp'. This is my
attempt:

DELETE *
FROM Target
WHERE Target.SerialNumber =
(SELECT SerialNumber
FROM Temp)

When I try to run the query I get the message:"At most one record can be
returned by this subquery"

If I run the SELECT statement on its own I get a 20 row result.

Have I got the SQL wrong or is it a restriction in Access, please?

Ken Ellis
 
R

Randy Harris

Ken Ellis said:
I have a table 'Temp' with two columns(SerialNumber and ProductDescription)
and 20 rows. I am trying to use this table to delete any rows in a table
called 'Target' with SerialNumbers that appear in 'Temp'. This is my
attempt:

DELETE *
FROM Target
WHERE Target.SerialNumber =
(SELECT SerialNumber
FROM Temp)

When I try to run the query I get the message:"At most one record can be
returned by this subquery"

If I run the SELECT statement on its own I get a 20 row result.

Have I got the SQL wrong or is it a restriction in Access, please?

Ken Ellis
Click to expand...

Ken, I didn't test this to confirm that it would work, but I believe what
you want is:

DELETE *
FROM Target
WHERE Target.Serialnumber IN
(SELECT SerialNumber FROM Temp)

HTH,
Randy
 
G

Guest

Ken,

Try this syntax:

DELETE
FROM Target
WHERE Target.SerialNumber IN
(SELECT SerialNumber
FROM Temp)

The "*" is optional (AFAIK).

I saw an answer by Ofer earlier in the Queries forum where he used "IN"
instead of "=".

So I threw together a couple of tables and tried the query using your
syntax; I got the same error message about "..one record..".

Then I changes the "=" to "IN" and it deleted the records.

HTH
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Syntax for nested joins on 3 tables 2
Transform Rows to Columns 1
Help with multiple table queries 2
AutoIncrement Text Field in an Insert Statement 12
System.Collections.Generic.IList<MicroLite.SerialNumber>' is lessaccessible than method 1
append query creating new records 2
Referring to fields in a subquery 1
not in criteria fails 2

Top