CODEWITHSUNDEEP
mysqlsqldatabase
Harjeet Jadeja
Harjeet Jadeja

Reputation: 1604

getting error #1242 while deleting data from the table

I am trying to delete data from the table and my query is as below:

delete from mybadge where uid=5 and badge_name=(select concat(tag_name,'-Beginner') from tag,post_tags where post_tags.tag_id=tag.tag_id);

I am getting 5 records by select statement, I want to delete all that 5 records.

Upvotes: 1

Views: 100

Answers (3)

Santosha Epili
Santosha Epili

Reputation: 315

use- 'in' keyword instead of '=' sign

delete from mybadge where uid=5 and badge_name in (select concat(tag_name,'-Beginner') from tag,post_tags where post_tags.tag_id=tag.tag_id);

Upvotes: 2

CloudyMarble
CloudyMarble

Reputation: 37566

If the select statement is working fine for you then just delete all uid's returned by the select:

DELETE FROM mybadge WHERE uid IN(
SELECT uid
FROM mybadge 
WHERE uid=5 
AND badge_name=(SELECT concat(tag_name,'-Beginner') 
                FROM tag,post_tags 
                WHERE post_tags.tag_id=tag.tag_id)
);

Upvotes: 0

NickJ
NickJ

Reputation: 9559

Your sub-query, as you stated, returns 5 rows. However, the badge_name cannot have 5 different values in the same row. It's a bit like saying:

 delete from mybadge where uid=5 and badge_name=(5 different values)

try

delete from mybadge where uid=5 and badge_name in (select concat(tag_name,'-Beginner') from tag,post_tags where post_tags.tag_id=tag.tag_id);

Upvotes: 1

Related Questions