mysql query to delete records that match multiple fields

Question

I have a database that looks like this:

CREATE TABLE cargo (
    cargodate int unsigned NOT NULL,
    cargoname CHAR(128) NOT NULL,
    lattitude double NOT NULL,
    longitude double NOT NULL,
    user CHAR(64) NOT NULL
) type=MyISAM;

I want to make sure there are no more than 5 entries for this cargo at the same location with the same user. A user can have multiple entries as long as they are in different locations (lattitude, longitude).

How do I make my sql INSERT statement take care of this?

Right now I execute:

   INSERT INTO cargo VALUES (UNIX_TIMESTAMP(), '{$cargoname}', '{$lat}', '{$lng}', '{$user}');

I can do a DELETE FROM, but I want to only delete entries if there are more than 5. In that case I want to delete the oldest entries

Thanks Deshawnt

see http://stackoverflow.com/questions/913841/mysql-conditional-insert — ShinTakezou, Jul 25 '11 at 18:02
@ShinTakezou: He wants to invalidate the oldest row, not prohibit the new one. — Wrikken, Jul 25 '11 at 18:04
@Wrikken hm sorry; instead of invalidating, he could do an update over the record he would "invalidate", e.g. like `update ... set column = newvalue .... where condition of an invalid record`... though this way if the record does not exist, it would fail (and if they exist more records, he would update all); then maybe something like "update ... if exists, otherwise insert" query? — ShinTakezou, Jul 25 '11 at 18:08
Ermmmm, for identifying the oldest 5 entries (in case of more than 5 entries) there has to be a counter (per cargo/location/user) or a timestamp column — rabudde, Jul 25 '11 at 19:07

score 1 · Answer 1 · answered Jul 25 '11 at 19:17

1

You could use triggers http://dev.mysql.com/doc/refman/5.0/en/triggers.html Just after insert you may delete all not needed entries.

answered Jul 25 '11 at 19:17

Ruslan Polutsygan

4,401
2
26
37

score 0 · Answer 2 · answered Jul 25 '11 at 20:56

Using a trigger as mentioned by Ruslan Polutsygan would be the most natural solution.

If you don't want to mess around with them, then the alternative would be to run:

SELECT * FROM cargo ORDER BY cargodate DESC LIMIT 4,18446744073709551615

before you run an insert, and then delete the rows that are returned by that query, and then do the insert.

As a side-note, the number 18446744073709551615 was taken out of the example from the MySQL documentation:

To retrieve all rows from a certain offset up to the end of the result set, you can use some large number for the second parameter

mysql query to delete records that match multiple fields

2 Answers2