Geek asks and answers

Mysql insert if row does not already exist in table with NO UNIQUE FIELDS

Look for some time already for how to do this.

It seems like all solutions need unique fields with indexes.

+2
source share
4 answers

there is no IF NOT EXISTS syntax in INSERT , but you can use ON DUPLICATE KEY. Assuming you create a unique index on the first name, last name, your update could be read:

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

which makes a neutral insert.

+9
source

Have you already considered the syntax INSERT OR UPDATEfor mysql, or do you want the new insert to be canceled instead of returning to the update?

: , afftee, ON DUPLICATE KEY .

BEFORE INSERT, :

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

, , Nicolas Lescure, [mysql doc] [1]:

" " - (stop_action) (reason_to_stop). ( " " "" ) = > , (stop_action) ( " " ').

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

0
$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
      ";
0
source

The Mysql insert documentation does not have the syntax "insert if does not exist." To solve this problem, I predefine the use of the syntax "Paste ... select". In the selection part, you must add the where clause using the "does not exist" 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;
-1
source

More articles:


All Articles