mysql Delete all records from a table except the first

Question

Does anyone know where I’m going wrong? or if it’s 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 idorigem field always keeps the same value as the first record which is what I don’t want to be deleted.

example:

ID IDORIGEM
387 387
490 387
510 387
650 387

mysql is giving this error message:

1241 - Operand should contain 1 column(s)

Answer 1

Just for the record, this error appears when you try to modify a table and at the same time use it in a subquery. This serves to protect DB consistency.

In your case, there’s no reason for the subquery exist, for it is enough to do this:

DELETE FROM reservas WHERE id != 387

There’s no point in getting an ID that you already know what it is.

---

Updating the response, since the question has changed (but the logic is the same).

If you want to preserve all 387, regardless of which of the two fields is:

DELETE FROM reservas WHERE id != 387 AND idorigem != 387

That is, delete from reservas the records whose id is not 387 and idorigem not be 387.

As for the mistake, it’s because you’re using SELECT id, idorigem in an expression waiting for only one column. If only one SELECT id, solves error 1241, and goes back to 1093, which was wisely created to avoid conflicting use of subquery.