Check if it already exists in the table before entering record

Asked

Viewed 4,792 times

4

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

As I have a column ID with as property AUTO INCREMENT, I can’t use the 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 the query to check whether or not the record exists before insertion?

mysql sql database

  • You will insert the data directly into mysql, or you will use some script for this?

  • @Leoletto Preferably the check is directly in Mysql. Example: If it doesn’t exist: insert, else: do nothing.

2 answers

8


You can do the INSERT straight from a 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)

  • Ola Sorack, syntax error in both cases.

  • WHERE NOT EXISTS works on Mysql?

  • Version 5.7.14...

  • ERROR: "You have a syntax error in your SQL next to 'WHERE NOT EXISTS(SELECT..."

  • All right, no problem.

  • 1

    You made a mistake here just now, maybe because of SELECT FROM. Placed SELEC 1 FROM. Following your reasoning, I was able to solve the problem this way: INSERT INTO relacao(user_1, user_2)
SELECT a.user_1,
 a.user_2
FROM (SELECT 'Pedro' user_1, 'Laura' user_2) a 
WHERE NOT EXISTS(SELECT 1
 FROM relacao
 WHERE user_1 = 'Pedro'
 AND user_2= 'Laura') If you want to change there, I give the credits to you. Thanks

  • the return of this addition would be 'true' for both cases (existing or not) would have to control this return?

Show 2 more comments

3

Using the ID number you can use the following:

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

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

  • This is the best answer.

  • @Tiagoluz I think you’re mistaken. The identifier is autoincrement, when I make a INSERT, I don’t pass the ID.

  • In case the question does not cite whether it stores this value after insertion, then its answer is only partial. You can try incrementing it by showing how you can catch the last ID inserted to use in your solution

  • 1

    @seamusd yes, true.. had not seen this. But you have another option to use replace .... remove the auto-increment primary key ID and put user_1 and user_2 as the composite primary key. Then replace works perfectly, and I believe with a better performance than proposed solution. Hug and good luck! =)

Browser other questions tagged mysql sql database

You are not signed in. Login or sign up in order to post.