CODEWITHSUNDEEP
mysqlinsert
Question
Question

Reputation: 145

Mysql query insert into only if select count > n

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.

Upvotes: 1

Views: 2290

Answers (1)

Barmar
Barmar

Reputation: 782683

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.

Upvotes: 2

Related Questions