MySQL: Is it possible to "INSERT if number of rows with a specific value is less than X"?

Question

To give a simple analogy, I have a table as follows:

id (PK) | gift_giver_id (FK) | gift_receiver_id (FK) | gift_date

Is it possible to update the table in a single query in such a way that would add a row (i.e. another gift for a person) only if the person has less than 10 gifts so far (i.e. less than 10 rows with the same gift_giver_id)?

The purpose of this would be to limit the table size to 10 gifts per person.

Thanks in advance.

Answer 1

try:

insert into tablename
   (gift_giver_id, gift_receiver_id, gift_date)  
select GIVER_ID, RECEIVER_ID, DATE from Dual where  
   (select count(*) from tablename where gift_receiver_id = RECEIVER_ID) < 10

Answer 2

I'm no SQL guru but I'm thinking something like the following should work: (assuming a table name of gifts):

INSERT INTO gifts (gift_giver_id, gift_receiver_id,gift_date)
SELECT DISTINCT senderidvalue,receiveridvalue,datevalue FROM gifts
WHERE (SELECT COUNT(*) FROM gifts WHERE gift_giver_id = senderidvalue ) < 10;

[edit] Code formatting doesn't like me :(

Answer 3

"And would that also be, 'otherwise, update the fields in the oldest row'?"

And would that also be, a rather bloody significant annendum :P

I wouldn't do something that complex in a single query, I'd select first to test for the oldest and then either update or insert accordingly.

Not knowing what language you're working in other than SQL, I'll just stick to pseudocode for non-SQL portions.

SELECT TOP 1 id FROM gifts
WHERE (SELECT COUNT(*) FROM gifts WHERE gift_giver_id = senderidvalue
ORDER BY gift_date ASC) > 9;

{if result.row_count then}

INSERT INTO gifts (gift_giver_id, gift_receiver_id,gift_date)
VALUES val1,val2,val3

{else}

UPDATE gifts SET gift_giver_id = 'val1',
gift_receiver_id = 'val2',gift_date = 'val3'
WHERE {id = result.first_row.id}

The problem with your request is you're trying to find a single query to perform a SELECT as well as either an INSERT or an UPDATE. Someone may well come along and call me out on this to prove me wrong but I think you're asking for the impossible unless you want to get into stored procedures.