CODEWITHSUNDEEP
mysql
Chris
Chris

Reputation: 59511

INSERT INTO if conditions are met

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?

Upvotes: 0

Views: 77

Answers (3)

Barranka
Barranka

Reputation: 21047

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:

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.

Upvotes: 2

Hansraj
Hansraj

Reputation: 174

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

Upvotes: 3

John Ruddell
John Ruddell

Reputation: 25842

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;

Upvotes: 2

Related Questions