mysql 小于多少行_MySQL：是否可以“如果具有特定值的行数小于X，则插入”？...

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.

解决方案

"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.