CODEWITHSUNDEEP
mysqlif-statementexistssql-insert
Reza
Reza

Reputation: 85

MySQL insert into table if all columns entry for a row does not exists

So I'm trying to avoid calling the table, see if I have the entry or not in order to insert a new row, or update it. I'm trying the below query, but I get syntax errors. can someone please help.

Here's my query:

IF(
  NOT EXISTS(
    SELECT
        *
    FROM
        ranking_rank_data
    WHERE
        rank = '1'
    AND source_id = '1'
    AND ranking_school_id = '2'
  )
) THEN INSERT INTO 
     ranking_rank_data (rank, source_id, ranking_source_id VALUES ('1','2','3')
END IF;

Upvotes: 0

Views: 334

Answers (3)

Gordon Linoff
Gordon Linoff

Reputation: 1269693

You can also do this by creating a unique index on the three values:

create unique index on ranking_rank_data_rank_source_id_ranking_source_id on
    ranking_rank_data(rank, source_id, ranking_source_id);

This will prevent duplicates. Then you can insert records (or update records) confident of no duplicates. There are two ways (apart from the not exists approach) to do this. The first is insert ignore:

INSERT IGNORE INTO ranking_rank_data (rank, source_id, ranking_source_id)
SELECT '1','2','3';

This ignores all errors, not just duplication errors, so I prefer the second method, which uses on duplicate key update:

INSERT IGNORE INTO ranking_rank_data(rank, source_id, ranking_source_id)
    SELECT '1','2','3'
    ON DUPLICATE KEY UPDATE rank = values(rank);

(The update statement is a no-op that ignores the error.)

Upvotes: 1

Capsule
Capsule

Reputation: 6159

Why not using the INSERT ON DUPLICATE KEY UPDATE syntax and set a unique key on rank + source_id + ranking_school_id? You could update a dummy field if you don't have anything to "really" update. See https://dev.mysql.com/doc/refman/5.0/en/insert-on-duplicate.html

You could also just try to insert it. It will fail on duplicate key but you could handle this error in the programming language you're using on top of MySQL.

Upvotes: 0

fthiella
fthiella

Reputation: 49049

You could use an INSERT ... SELECT query, selecting from dummy table DUAL and putting the where clause there, if the condition is false no row will be inserted:

INSERT INTO ranking_rank_data (rank, source_id, ranking_source_id)
SELECT '1','2','3'
FROM dual
WHERE
  NOT EXISTS(SELECT *
             FROM ranking_rank_data
             WHERE rank = '1' AND source_id = '1' AND ranking_school_id = '2')

Please see fiddle here.

Upvotes: 1

Related Questions