CODEWITHSUNDEEP
mysqlsqlmysql-workbench
sam
sam

Reputation: 97

Delete from a MYSQL subquery

I'd like to delete all the records in a table matching the subquery as so:

delete from device where clientMac =   (select * from device  where clientMac = '80:d6:05:02:1d:b9');

I'm getting this error:

Error Code 1241: Operand should contain 1 column

I want to also avoid:

Error 1093: You can't specify target table for update in FROM clause

Upvotes: 0

Views: 91

Answers (5)

Asif patel
Asif patel

Reputation: 35

Here id field is your primary key of table. 

delete 
from device 
where id in (select * 
             from (select id 
                   from device 
                   where FIND_IN_SET(clientMac,'80:d6:05:02:1d:b9')) 
             as t1 )

Upvotes: 1

Deepak Tripathi
Deepak Tripathi

Reputation: 17

If i understand your query right then your query doesn't require subquery. Please look this:

DELETE FROM device WHERE clientMac IN (SELECT clientMac FROM device where clientMac='80:d6:05:02:1d:b9')

Is equivalant to :

DELETE FROM device WHERE clientMac='80:d6:05:02:1d:b9';

I think you wanted to achieve something else. Please confirm.

Upvotes: 2

Mirza Obaid
Mirza Obaid

Reputation: 1717

you have to use IN

DELETE FROM device WHERE clientMac IN (SELECT clientMac FROM device where clientMac='80:d6:05:02:1d:b9')

Upvotes: 0

Ankit Agrawal
Ankit Agrawal

Reputation: 2454

delete from device 
where clientMac in (
      select * from 
      (select clientMac  from device where clientMac = '80:d6:05:02:1d:b9')as t
    );

Upvotes: 1

Naruto
Naruto

Reputation: 4329

The error is due to mismatch of columns. Your select query must only return column clientMac and also if there are multiple result expected then use IN Query like below

delete from device where clientMac IN (select clientMac from device where clientMac = '80:d6:05:02:1d:b9');

Upvotes: 1

Related Questions