CODEWITHSUNDEEP
sqlsql-delete
Kimo Atomix
Kimo Atomix

Reputation: 21

query dont want to work very well ^^

DELETE FROM _Inventory 
where ItemID = ( 
    SELECT * 
    FROM _Items 
    WHERE RefItemID IN ( 46440 , 46441 , 46442 ) 
        AND ID64 = @ID64 )

Only one expression can be specified in the select list when the subquery is not introduced with EXISTS.

Upvotes: 1

Views: 45

Answers (4)

Explosion Pills
Explosion Pills

Reputation: 191729

Change it to SELECT ItemID FROM _Items .... The error is pretty clear: your subquery is not allowed to return more than one column because ItemID can only be compared to the single column. In EXISTS this would be equivalent to returning 1 or 0, which is still a single column and thus allowed.

Upvotes: 0

Brian
Brian

Reputation: 1387

the star should be replaced with the correct column name. Also make sure the subquery returns only one row. Not multiple rows. If multiple rows, then need to change your equality comparison to a IN clause instead.

Upvotes: 0

John Woo
John Woo

Reputation: 263703

simply use IN

DELETE 
FROM _Inventory 
where ItemID IN ( SELECT ItemID 
                  FROM _Items 
                  WHERE RefItemID IN (46440 ,46441 ,46442) 
                        AND ID64 = @ID64 
                )

or by using JOIN which I more prefer,

DELETE  a
FROM    _Inventory a
        INNER JOIN _Items b
            ON a.ItemID = b.ItemID
WHERE   b.RefItemID IN (46440, 46441, 46442) AND
        b.ID64 = @ID64

Upvotes: 1

sgeddes
sgeddes

Reputation: 62841

You need to use IN and specify a field from your items table (instead of specifying *):

DELETE FROM _Inventory 
WHERE ItemID IN (
    SELECT ItemId 
    FROM _Items 
    WHERE RefItemID IN ( 46440 , 46441 , 46442 ) AND ID64 = @ID64 )

Upvotes: 1

Related Questions