CODEWITHSUNDEEP
mysql
noober
noober

Reputation: 4938

Insert into a table, if there is no such rows

I have a sessions table with the 'userid' and 'expired' columns. I want to check, whether any row with the given user ID and not expired exists and insert a new row, if no rows found.

I've read about INSERT IGNORE, but (userid + expired) cannot be a key, since it's possible to have multiple rows with the same users (all expired), I cannot only have more than 1 not expired user.

I tried this, but to no avail ('You have an error...'):

IF (SELECT 1 FROM sessions WHERE user = :user AND expired = 0) <> 1
INSERT INTO sessions (user) VALUES(:user)

('expired' is 0 by default). mySQL version is 5.

UPDATE.

I've tried this as well:

IF NOT EXISTS(SELECT 1 FROM sessions WHERE user = 0 AND expired = 0)
    INSERT INTO sessions (user) VALUES(0)

using HeidySQL 7. It doesn't work neither. mySQL version is 5.5.

UPDATE2. Logical errors in my statement fixed.

Regards,

Upvotes: 0

Views: 918

Answers (4)

noober
noober

Reputation: 4938

The problem with my statement is that IF EXISTS is supposed to be in a stored procedure. Nevertheless, the function IF() works:

SELECT IF(EXISTS(SELECT 1 FROM sessions WHERE user = 0 AND expired = 0), 0, 1)

Now I'm looking for how to equal plain value like 0 and INSERT INTO statement. This doesn't work:

SELECT IF(EXISTS(SELECT 1 FROM sessions WHERE user = 0 AND expired = 0), 0,
INSERT INTO sessions (user) VALUES (0) SELECT)

If it was C++, I'd say 'type mismatch between ?: operands' and use comma operator. In SQL it doesn't work.

UPDATE

Well, I've just created a stored routine. Just because I cannot use IF otherwise. What a stupid RDBMS this mySQL is!

Upvotes: 0

Steve Robbins
Steve Robbins

Reputation: 13812

INSERT IGNORE INTO sessions (user) VALUES(:user)

Upvotes: 0

Gonzalo.-
Gonzalo.-

Reputation: 12672

use If exists clause (in this case, not exists)

IF NOT EXISTS(SELECT 1 FROM sessions WHERE user = 0 AND expired = 0)
    INSERT INTO sessions (`user`) VALUES(:user)

edit

    INSERT INTO sessions (`user`) select user from 
    (select :user as user from sessions where not exists(SELECT 1 FROM sessions WHERE user = 0 AND expired = 0)) T1

then put 0 or the value you want harcoded in :user

    INSERT INTO sessions (`user`) select user from 
    (select 0 as user from sessions where not exists(SELECT 1 FROM sessions WHERE user = 0 AND expired = 0)) T1

Upvotes: 2

Bulbasaur
Bulbasaur

Reputation: 714

Elvieejo's solution seems good, except looks like you'd have to use not exists going by the problem stated.

IF NOT EXISTS(SELECT 1 FROM sessions WHERE user = 0 AND expired = 0)
    INSERT INTO sessions (user) VALUES(:user)

Upvotes: 1

Related Questions