Query INSERT with WHERE condition

Asked

Viewed 13,421 times

5

How can I make an Insert with a Where condition?

I have the following query:

INSERT INTO `registro`(`id`, `username`)

values

('','USER_1'),
('','USER_2'),
('','USER_3')

It will add a new user to the record table. Only that I need him to check if this user already exists in DB, if it already exists he should not enter. Such verification shall be made only for the column username.

I read in some places that the INSERT does not accept the WHERE, in this case as it should be done?

mysql sql

4 answers

14


In SQL da to do so

  INSERT INTO pessoa (id, nome, sexo, datanascimento, cpf) 
       SELECT 227,'FULANDO DE TAL','F','1999-09-09', '999.999.999-9'
WHERE NOT EXISTS (SELECT 1 FROM pessoa WHERE id = 227);

  • I was exactly writing this answer. The fastest leads.

  • @WilliamAparecidoBrandino hahahahhaa.

  • @Daniellearrudatorres And how to make more than one line to be inserted?

  • @Wendler SELECT will take care of it.

  • So, is that I need to include more than one record in the same command, then I must repeat the whole query, or just the SELECT?

  • Put the part of your code so we can better understand your need.

  • I updated the question there, in this case it will enter 3 records.

  • If you need to check all values, it is necessary to pass all of them by select, as they are different values and then the return may be different too.

  • And how to do this by repeating all the SELECT? Sorry, I am very lay in mysql.

  • Because of 1, in SELECT 1, in WHERE NOT EXISTS?

Show 5 more comments

3

The Mysql cannot stand the where in the insert, but you can use insert with select 

INSERT INTO Users(weight, desiredWeight) 
    SELECT weight, desiredWeight 
    FROM AnotherTable 
    WHERE id = 1

can have more details on how to do here

then I managed to make some filter, in case the line already exists and you will add more information I suggest update

2

As @Renan has already mentioned, it is not allowed WHERE in the INSERT, then use a SELECT how the @Danielle response will solve your case. It seems to me that the key to your table is the column id and not username, but if I were to validate the key field I could use IGNORE at your command.

To illustrate to anyone who has the same doubt, but the validation is in the key field, you can simply use INSERT IGNORE: https://dev.mysql.com/doc/refman/5.7/en/insert.html

In this case, the command will be ignored if it violates the primary key, ie already exists, and the syntax would be the following in your example: 

INSERT IGNORE INTO `registro`(`id`, `username`)
values
('','USER')

  • No, the primary key is the column id, but I need him to check the column username.

  • I imagined, then the insert with select should be the best option for you, but I leave it on record here if someone has a different scenario, and wishes to use the ignore

  • Perfect, thank you for the answer. :)

  • you know some way to use the insert with select, but for several values? I updated the question for a better example.

  • with a single command I think will not be possible... the most practical solution would be to insert into a temporary table, then insert into the table registration only values that do not exist. Ai da para fazer o Insert com select, that is, two Insert commands

1

I searched the same thing and as I did not see a solution created mine in Mysql I have an N-N relationship table but some records weren’t on it I made a query in the 2 tables BASE_A and BASE_B forcing a cartesian product

    SELECT BASE_A.ID_A, BASE_B.ID_B FROM BASE_A, BASE_B

(Without placing on Join or WHERE with table N-N)

And then I Where with NOT IN on the table that should have the data

    select BASE_A.ID_A, BASE_B.ID_B 
    FROM BASE_A, BASE_B 
    where (BASE_A.ID_A, BASE_B.ID_B) not in (
    select ID_A,ID_B from TABELA_N_N )

And it worked as expected

Browser other questions tagged mysql sql

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