Trying to delete from Join and I know I have funky spacing and I know I have dup IMEI's

Question

DELETE dbo.bBoxDetail
FROM dbo.bBoxDetail AS BD
INNER JOIN dbo.bBoxHeader AS BH ON LTRIM(RTRIM(BD.bBoxDetailId)) = LTRIM(RTRIM(BH.bBoxId))
WHERE LTRIM(RTRIM(BD.ESNs)) = (SELECT LTRIM(RTRIM(IMEI)) FROM dbo.tmpIMEI)

I get this error:

Msg 512, Level 16, State 1, Line 1 Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.

BD.ESNs is NVARCHAR(50) and IMEI is NVARCHAR(30) I have duplicate ESNs and I want to delete all ESNs that match the SELECT ... IMEI

In order to format your code, you can highlight the block and use the `{}` from the editing toolbar. — Taryn, Jul 09 '13 at 16:06
See also [How Do I Format My Code Blocks?](http://meta.stackexchange.com/questions/22186/how-do-i-format-my-code-blocks) — chue x, Jul 09 '13 at 16:07

Aleksandr Fedorenko · Answer 1 · 2013-07-09T16:11:34.353

0

Use IN operator in WHERE clause

DELETE dbo.bBoxDetail
FROM dbo.bBoxDetail AS BD INNER JOIN dbo.bBoxHeader AS BH ON LTRIM(RTRIM(BD.bBoxDetailId)) = LTRIM(RTRIM(BH.bBoxId))
WHERE LTRIM(RTRIM(BD.ESNs)) IN (SELECT LTRIM(RTRIM(IMEI)) FROM dbo.tmpIMEI)

but preferred option is EXISTS operator

DELETE dbo.bBoxDetail
FROM dbo.bBoxDetail AS BD INNER JOIN dbo.bBoxHeader AS BH ON LTRIM(RTRIM(BD.bBoxDetailId)) = LTRIM(RTRIM(BH.bBoxId))
WHERE EXISTS (
              SELECT 1
              FROM dbo.tmpIMEI
              WHERE LTRIM(RTRIM(BD.ESNs)) = LTRIM(RTRIM(IMEI))
              )

edited Jul 09 '13 at 16:11

answered Jul 09 '13 at 16:06

Aleksandr Fedorenko

16,594
6
37
44

Both of these queries only seem to delete one row. tmpIMEI has 500+ but I cannot get more than one hit if I convert this to a select. – TooMuchToLearn Jul 18 '13 at 19:11

score 0 · Accepted Answer · answered Jul 09 '13 at 16:07

If you read the error

Msg 512, Level 16, State 1, Line 1 Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.

Pay attention to Subquery returned more than 1 value as this is the error, you are returning any number of rows from your subquery:

The correct SQL is below:

DELETE dbo.bBoxDetail
FROM dbo.bBoxDetail AS BD
INNER JOIN dbo.bBoxHeader AS BH ON LTRIM(RTRIM(BD.bBoxDetailId)) = LTRIM(RTRIM(BH.bBoxId))
WHERE LTRIM(RTRIM(BD.ESNs)) = (SELECT TOP 1 LTRIM(RTRIM(IMEI)) FROM dbo.tmpIMEI)

Trying to delete from Join and I know I have funky spacing and I know I have dup IMEI's

2 Answers2

Linked