Check if it already exists in the table before entering record

Question

Check if it already exists in the table before entering record

Navigation

#1 by (6 votes)
#2 by (2 votes)

3

I need to include a record in a table, however before inclusion to avoid duplication it is necessary to do a check.

As I have a ID column with property AUTO INCREMENT , I can not use INSERT IGNORE INTO .

Table

CREATE TABLE relacao (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    user_1 varchar(24) NOT NULL,
    user_2 varchar(24) NOT NULL
)

Example

INSERT INTO relacao(user_1, user_2) values("Pedro", "Laura")
INSERT INTO relacao(user_1, user_2) values("Pedro", "Laura") /* não deixar inserir*/

How would query be to check whether or not the record exists before insertion?

mysql sql

asked by anonymous 08.12.2016 / 16:46

2 answers

2

Using the ID number you can use the following:

   REPLACE INTO relacao VALUES ( 1, 'Pedro', 'Laura' )

If there is no id 1 it will insert the record and if there is it will update.

08.12.2016 / 18:15

Character manipulation in PHP Sort Dropdown with jquery after append [duplicate]

score 6 · Accepted Answer

You can make INSERT direct from query .

INSERT INTO relacao(user_1, user_2) 
SELECT x.user_1, x.user_2 
  FROM (SELECT 'Pedro' user_1, 'Laura' user_2) x
 WHERE NOT EXISTS(SELECT 1 
                    FROM relacao r 
                   WHERE r.user_1 = x.user_1
                     AND r.user_2 = x.user_2)