MySQL: is it possible to "INSERT if the number of rows with a certain 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 a table in one query in such a way as to add a row (i.e. another gift for a person) only if a person has less than 10 gifts (i.e. less than 10 rows with the same gift_giver_id)?

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

Thanks in advance.

Answer 1

"And will it also:" otherwise update the fields in the oldest row? "

: P

- , , , .

, , SQL, , SQL.

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}

, SELECT, INSERT UPDATE. - , , , , , .

Answer 2

:

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 3

SQL, , - : ( , ):

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] : (