Mysql query insert into only if select count > n

Question

I have 2 queries:

SELECT COUNT(*) FROM a WHERE id = 1
//if row == 1
INSERT INTO a VALUES(fielda) VALUES('value')

Is there a way to merge these two queries into one? I tried with 'IF (count> 0, ..)' and similar, but the query is incorrect. This involves inserting a new record into the table, taking care not to exceed a pre-set number of records for each field. In theory it should be similar to an INSERT IF ...

Edit:

@Barmar I tried but I think I did not understand what you wrote (in fact I made a mistake in the query), I try to answer like this:

THE QUERY AFTER YOUR RESPONSE:
INSERT INTO table1 SELECT MAX(id) FROM table1 WHERE field1 = (SELECT id from a WHERE f = field2) HAVING COUNT(*) = 1 (all fields request) VALUES (all values request)

//field1 = id from table2
//field2 = id from another table: associative value

//ORIGINAL QUERY
//FIRST COUNT:
SELECT COUNT(*) from table1 WHERE field1 = (SELECT id FROM table2 WHERE f = field2)
//AFTER THE INSERT:
INSERT INTO table1 (all fields request) VALUES (all values request)

I came to mind this example I try to show you:

TABLE PLAYER: fields(ID, TEAMID, NAME) => (id=int, teamid=int associate to table team, name=varchar)

TABLE TEAM: fields(ID NAME) => (id=int, name=varchar)

Suppose that the players in a team are maximum 20, so you expect maximum 20 records associated by the player table for the same teamid value, or at least this is what we humans think, because for the computer can also be infinite. I was looking for a way to allow the insertion only in the case in which it is actually permissible to insert records, in this case the condition is that in the players table there are less than 21 records per team.

What is fielda? Is it a column from the row in table a where id = 1? — Barmar
– Barmar, Commented Sep 3, 2018 at 23:01
I think you need to clarify what you're actually trying to do. Show an example of the original contents of the tables, and what you want to insert. — Barmar
– Barmar, Commented Sep 3, 2018 at 23:29
An INSERT statement either uses VALUES to list the values to be inserted, or SELECT to get the values from a query. You can't use both in the same insertion. — Barmar
– Barmar, Commented Sep 3, 2018 at 23:30
fielda (in the first example) is a common field of first table. you have right I was wrong to write: don't exist b, exist only a — Question
– Question, Commented Sep 3, 2018 at 23:30
Is there one table or two tables? The edit at the bottom has tables named table1, table2 and a. — Barmar
– Barmar, Commented Sep 3, 2018 at 23:33

Barmar · Accepted Answer · 2018-09-04 00:20:42Z

2

You can use INSERT ... SELECT, and put the condition in the SELECT query.

INSERT INTO player (teamid, name)
SELECT @teamid, @playername
FROM DUAL
WHERE (SELECT COUNT(*) FROM player
        WHERE teamid = @teamid) < 20

DUAL is a dummy table when you need to select literal data, but need to include other clauses in the query.

edited Sep 4, 2018 at 0:20

answered Sep 3, 2018 at 23:02

Barmar

789k57 gold badges555 silver badges669 bronze badges

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

3 Comments

Question Over a year ago

Try to see the example

Sanjay Salunkhe Over a year ago

Will this work in race condition?

Barmar Over a year ago

There's a transaction around the query, so it should.

Collectives™ on Stack Overflow

Mysql query insert into only if select count > n

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related