August 13, 2003 at 9:00 am
I am trying to delete multiple records from a table based on the tables inner join to another table (also tried to accomplish same task using an IN.. clause). I cannot make the delete work. It is analagous to the following:
DELETE FROM tableA FROM tableA INNER JOIN tableB ON tableA.par1 = tableB.par1 AND tableA.par2=tableB.par2 WHERE tableA.par1='ABC' AND tableB.par3='XYZ'
I get an error saying the subquery returns multiple records. I tried to accomplish the same using:
DELETE FROM tableA where par1='ABC' and par2 IN (SELECT par2 from TableB Where par1='ABC' AND par3='XYZ')
I get the same error. Any suggestions?
August 13, 2003 at 9:04 am
Does TableA in the real data have an ID value? If so, try this:
DELETE tableA
WHERE tableA.ID IN
(Select Distinct TableA.ID
FROM TableA
INNER JOIN tableB
ON tableA.par1 = tableB.par1
AND tableA.par2=tableB.par2
WHERE tableA.par1='ABC'
AND tableB.par3='XYZ')
August 14, 2003 at 3:34 pm
quote:
Does TableA in the real data have an ID value? If so, try this:DELETE tableA
WHERE tableA.ID IN
(Select Distinct TableA.ID
FROM TableA
INNER JOIN tableB
ON tableA.par1 = tableB.par1
AND tableA.par2=tableB.par2
WHERE tableA.par1='ABC'
AND tableB.par3='XYZ')
August 14, 2003 at 3:36 pm
quote:
Does TableA in the real data have an ID value? If so, try this:DELETE tableA
WHERE tableA.ID IN
(Select Distinct TableA.ID
FROM TableA
INNER JOIN tableB
ON tableA.par1 = tableB.par1
AND tableA.par2=tableB.par2
WHERE tableA.par1='ABC'
AND tableB.par3='XYZ')
I still get the same error - subquery returned more than 1 value. This is not permitted when subquery .... Here is the actual query (and excuse the name of the ID field - don't know who named it autoID):
DELETE [2002_TblFrm8582wsType]
WHERE autoID IN
(SELECT DISTINCT [2002_TblFrm8582wsType].autoID
FROM [2002_TblFrm8582wsType] INNER JOIN
[2002_TblBatch] ON
[2002_TblFrm8582wsType].F0001N = [2002_TblBatch].F0001N
AND
[2002_TblFrm8582wsType].statusCode = [2002_TblBatch].statusCode
WHERE Finalized = 0 AND
[2002_TblFrm8582wsType].statusCode = 'KanAm02')
August 14, 2003 at 6:31 pm
Try this
Delete[2002_TblFrm8582wsType]
FROM [2002_TblFrm8582wsType] t1
INNER JOIN[2002_TblBatch] b ON t1.F0001N = t1.F0001N
AND t1.statusCode = b.statusCode
WHERE Finalized = 0 AND --Missing table name for finalized
t1.statusCode = 'KanAm02' and [2002_TblFrm8582wsType].AutoID = t1.AutoID
Signature is NULL
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply