CODEWITHSUNDEEP
phpmysqlsql
Menztrual
Menztrual

Reputation: 41587

SQL Delete from with sub query error

Evening all, I have the following SQL Query for PDO:

DELETE FROM group_members WHERE group_id IN( SELECT * FROM groups WHERE group_owner = 1 ) AND user_id = 2

And for some strange reason I keep getting the following message:

#1241 - Operand should contain 1 column(s)

Now; I understand what the message means but I can clearly see that I've set a condition after the and so im not too sure what's going on.

Thanks for any help! :o) I'm sure its a noob mistake ;)

Upvotes: 0

Views: 110

Answers (4)

sujal
sujal

Reputation: 1050

Try this:

DELETE FROM group_members
WHERE group_id
    IN ( SELECT group_id FROM groups WHERE group_owner = 1 )
  AND
    user_id = 2

Upvotes: 1

Ankit Sharma
Ankit Sharma

Reputation: 4071

Give it a try-

DELETE FROM group_members WHERE user_id = 2 and group_id IN( SELECT * FROM groups WHERE group_owner = 1 )

I have not tested this and does not even know your desired result, but give this a try.

Upvotes: 0

Dmytro Shevchenko
Dmytro Shevchenko

Reputation: 34591

I know you already have an answer, but also, consider using a join instead of the subquery:

DELETE gm.*
FROM group_members AS gm
JOIN groups g
  ON gm.group_id = g.id
WHERE gm.user_id = 2
  AND g.group_owner = 1

Upvotes: 1

Rene Pot
Rene Pot

Reputation: 24815

You use * in your subquery, you need to select the correct column:

SELECT group_id FROM groups WHERE group_owner = 1

Upvotes: 1

Related Questions