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.

And would that also be, 'otherwise, update the fields in the oldest row'? — Don
– Don, Commented Jan 7, 2010 at 5:39
Umm... not oldest in this case, but rather the gift date for the same giver/receiver pair. So in a way, the date reflects the latest gift between the two but maintaining max 10 gifts. In truth, the above is a simplification. The actual primary key is a VARCHAR combination of giver/receiver IDs and there's no INT primary key. Two columns only. — Tom
– Tom, Commented Jan 7, 2010 at 6:11

mynameiscoffey · Accepted Answer · 2010-01-07 05:34:32Z

3

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

answered Jan 7, 2010 at 5:34

mynameiscoffey

16.1k5 gold badges35 silver badges46 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Sanjay Salunkhe Over a year ago

Will this work In race condition?

nathanchere · Accepted Answer · 2010-01-07 05:48:06Z

2

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

answered Jan 7, 2010 at 5:48

nathanchere

8,10815 gold badges68 silver badges86 bronze badges

1 Comment

Tom Over a year ago

Thanks, yes, I started thinking along these lines after posting the question. I'm using PHP, and separating this into two parts makes sense, as I'll need to tie other actions to the situation where there are already 10 gifts (i.e. tell the user etc). Therefore, I'll pick this as the accepted answer.

nathanchere · Accepted Answer · 2010-01-07 05:38:10Z

1

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

answered Jan 7, 2010 at 5:38

nathanchere

8,10815 gold badges68 silver badges86 bronze badges

Collectives™ on Stack Overflow

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

3 Answers 3

1 Comment

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related