Query INSERT with WHERE condition

Navigation
2

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 table record. Except that I need it to verify that this user already exists in the DB, if it already exists it should not insert. This check should only be done for the username column.

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

    
asked by anonymous 12.04.2018 / 15:59

3 answers

10

In SQL 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);
    
12.04.2018 / 16:03
4

Mysql does not support where in insert , but you can use insert with select 

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

You can have more details on how to do it here

I've been able to do some filtering, if the line already exists and you'll add more information I suggest update

    
12.04.2018 / 16:04
3

As @Renan already mentioned, WHERE is not allowed in INSERT , so use SELECT as @ Danielle's answer will solve in your case. I think the key to your table is the column id and not username , but if you were to validate the key field could use IGNORE in your command.

To illustrate who has the same doubt, but validation is in the key field, you can simple use INSERT IGNORE : link

In this case, the command will be ignored if it violates the primary key, that is, it already exists, and the syntax would be as follows in its example: 

INSERT IGNORE INTO 'registro'('id', 'username')
values
('','USER')
    
12.04.2018 / 16:15
Calculate circle area If else with functions