mysql Delete all records in a table except the first one

4

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)

    
asked by anonymous 30.06.2016 / 21:52

1 answer

8

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 .

    
30.06.2016 / 21:54