CODEWITHSUNDEEP
javamysqlsql
XTop
XTop

Reputation: 255

Insert only if row doesn't exist

I am using PreparedStatement to prepare sql queries. I want to insert a row in table if and only if it doesn't exist.

I tried this -

INSERT INTO users (userId) VALUES (?) ON DUPLICATE KEY UPDATE userId = ?

But this will unnecessarily update the userId.

How can i insert the userId here ?

Upvotes: 1

Views: 2688

Answers (3)

Loek Bergman
Loek Bergman

Reputation: 2195

on duplicate key does not work correctly when the table is an innodb. It creates exactly the problem you are describing. If you need the functionality of an innodb, then should you first check the existence of the row, otherwise can you convert the table to a myisam table.

edit: if you need to check the existence of the row before the decision to insert or update, then would I advice you to use a stored procedure.

DELIMITER //
CREATE PROCEDURE adjustusers(IN pId int)
BEGIN
DECLARE userid int;
  select  count(id) into userid from users where id = pId;
  if userid = 1 then
   update users set id = pId where id = pId;
  else
   insert into users(id) values(pId);
  end if;
END //
DELIMITER ;

A stored procedure is precompiled, just as a prepared statement. Hence no SQL injection problems and some more functionality and only one call to the database.

Upvotes: 0

RiaD
RiaD

Reputation: 47640

You may use

 INSERT IGNORE INTO users (userId) VALUES (?)

But you should understand why do you want ignore errors.

Upvotes: 1

Andomar
Andomar

Reputation: 238176

INSERT  INTO users 
        (userId) 
SELECT  ?
WHERE   NOT EXISTS
        (
        SELECT  * 
        FROM    users 
        where   userId = ?
        )

Upvotes: 2

Related Questions