mysql insert if row does not exist already in a ta

Question

Looking for a while now already for how to accomplish this.

Seems that all solutions need unique fields with indexes.

Answer 1

Have you already considered the INSERT OR UPDATE syntax of mysql, or do you want the newest insertion to be 'cancelled' instead of reverting to an update ?

Edit: after reflexion, the exact syntax, as shown by afftee, of ON DUPLICATE KEY is only triggered by duplicate value on primary key or at least unique columns.

A possible solution would be to use a BEFORE INSERT trigger similar to this :

CREATE TRIGGER block_dup_insert BEFORE INSERT ON my_table
FOR EACH ROW BEGIN
  -- check condition and stop if it would be duplicated according to your logic
END

If you want to stop the trigger and prevent the actual insert, it seems like you can use this post from Nicolas Lescure found on [mysql doc][1] :

Another way to "STOP ACTION" is to create a table (stop_action) with just a primary key(reason_to_stop). Then you pre-fill this table with some text ('do not do that', 'or that either')=> to stop action, just do an insert into this table (stop_action) with any of the pre-filled value ('do not do that').

[1]: http://dev.mysql.com/doc/refman/5.1/en/create-trigger.html mysql create trigger documentation

Answer 2

$sql2="INSERT INTO site_shipping (ship_oid,ship_status)
         SELECT '$orderid', '$status'
         FROM site_shipping 
         WHERE NOT EXISTS 
          (SELECT * 
           FROM site_shipping 
           WHERE ship_oid = '$orderid'
          )
         LIMIT 1
      ";

Answer 3

There is no 'insert if not exists' syntax in Mysql insert documentation. To cicumvent this problem, I preconize to use the "Insert ... select" syntax. In the select part, you must add a where statement using "not exists" syntax. For example, you can use this:

    insert into tbl (firstname,lastname) select 'Jhon', 'Doe'
from tbl where not exists (select 1 from tbl where firstname = 'Jhon' and lastname = 'Doe') limit 1;

Answer 4

there is no IF NOT EXISTS syntax in INSERT, but you could make use of the ON DUPLICATE KEY mechanism. Assuming you create a unique index on firstname, lastname, your update might read:

INSERT INTO tb (firstname, lastname) 
VALUES ('Jack', 'Doe') 
ON DUPLICATE KEY UPDATE lastname = lastname;

which renders the insert neutral.