Can someone tell me where I'm going wrong? or is it possible to do this?
I wrote the query like this:
DELETE FROM reservas WHERE ID NOT IN (SELECT ID,IDORIGEM FROM reservas WHERE ID='387' and IDORIGEM='387');
The id field keeps always the same value as the first record which is what I do not want to be excluded.
example:
ID IDORIGEM
387 387
490 387
510 387
650 387
mysql is giving this error message:
1241 - Operand should contain 1 column (s)
Just to note, this error appears when you try to modify a table and at the same time use it in a subquery . This is to protect the consistency of the DB.
In your case, there's no reason for subquery to exist, just do this:
DELETE FROM reservas WHERE id != 387
There's no sense in looking for an ID that you already know what it is.
Updating the answer , since the question has changed (but the logic is the same).
If you want to preserve all 387, regardless of which of the 2 fields is:
DELETE FROM reservas WHERE id != 387 AND idorigem != 387
That is, delete% with% of records whose reservas is not 387 and whose id is not 387.
As for the error, it's because you're using idorigem in an expression that only expects a column. If it only gives a SELECT id, idorigem , it resolves error 1241, and returns to pro 1093, which was wisely created to avoid conflicting use of subquery .