INSERT INTO if conditions are met

Question

A user is only meant to have up to 3 keys registered to his account at any one time. To add a new key, the user must first delete another key to "make room" for a new one.

I want this to be checked server-side, but I can't get the query to work. Here is what I tried:

IF (SELECT COUNT(serial_key_nbr)
FROM keys_table WHERE user_id = 9) <= 2
THEN INSERT INTO keys_table (user_id, serial_key_nbr)
VALUES (9, 'abc123')

How to do this?

you could make a function query that gets the number of keys and if the count is = 3 then say they need to delete 1 otherwise create the new key? — akaBase, Sep 13 '14 at 19:55

score 3 · Accepted Answer · answered Sep 13 '14 at 20:09

3

You can use the below mention Script for the same:

INSERT INTO keys_table (user_id, serial_key_nbr)
SELECT 9, 'abc123'        FROM DUAL
WHERE 
(SELECT COUNT(serial_key_nbr)
FROM keys_table WHERE user_id = 9)<=2

answered Sep 13 '14 at 20:09

Hansraj

174
5

Clean, simple and safe. Excelent answer – Barranka Sep 13 '14 at 20:19

score 2 · Answer 2 · answered Sep 13 '14 at 20:01

2

if you want to use an if to do a conditional select then I would put it in a variable like so.

BEGIN

    DECLARE var1 INT;

    SELECT COUNT(serial_key_nbr) INTO var1
    FROM keys_table 
    WHERE user_id = 9;

    IF var1 <= 2 
    THEN
      INSERT INTO keys_table (user_id, serial_key_nbr)
      VALUES (9, 'abc123')

    END IF;

answered Sep 13 '14 at 20:01

John Ruddell

25,283
6
57
86

this is meant to be inside a stored procedure, right? – Barranka Sep 13 '14 at 20:08
@Barranka yes.. the OP said he was using a stored procedure [HERE](http://stackoverflow.com/questions/25827061/insert-into-if-conditions-are-met/25827151#comment40404632_25827061) – John Ruddell Sep 13 '14 at 20:08

score 2 · Answer 3 · edited May 23 '17 at 12:05

A trigger might be the way to go. If a condition is met, a trigger before inserting in the table can perform an invalid operation and cause the insert operation to fail:

delimiter $$
create trigger keep_three before insert on keys_table for each row
begin
    if (select count(serial_key_nbr) from keys_table where user_id = new.user_id) >= 3 then
        insert into non_existent_table (non_existent_field) values (new.user_id);
    end if;
end$$
delimiter ;

Ugly, but it might work.

Reference:

"MySQL Triggers: How do you abort an INSERT, UPDATE or DELETE with a trigger?"

Another solution (better I think) is to forcibly delete an entry before attepting the insert. When there are less than 3 entries, the insert procedes normally:

delimiter $$
create trigger keep_three before insert on keys_table for each row
begin
    while (select count(serial_key_nbr) from keys_table where user_id = new.user_id) >= 3 do
        delete from keys_table where user_id = new.user_id 
        -- OPTIONAL: Add an ordering criteria to define which entry is deleted first
        limit 1;
    end while;
end$$
delimiter ;

I think this is cleaner.

A third way (I've found it here). It will return an error message (by signaling sqlstate 45000: Unhandled user defined exception) associated with the defined condition:

delimiter $$
create trigger keep_three before insert on keys table for each row
begin
    declare msg varchar(255);
    declare n int default 0;
    set n = (select count(serial_key_nbr) from keys_table where user_id = new.user_id);
    if n >= 3 then
        set msg = "INSERT failed: There must be only three entries for each user. Delete an entry first";
        SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = msg;
    end if;
end$$
delimiter ;

A cleaner version of my first option.

INSERT INTO if conditions are met

3 Answers3