NOT EXISTS in update?

Asked

Viewed 405 times

2

I have the following SQL:

insert into teste (id,nome)
select 2,'João' from dual 
where not exists(select * from teste where nome = 'João')

Would you like to do the same with update? Is there any way?

mysql sql

2 answers

3

Searching the web I found the INSERT command ... ON DUPLICATE KEY UPDATE, it is intended to insert if the record does not exist and update if it already exists, based on the key:

INSERT INTO teste (id, nome) VALUES(2, 'João') ON DUPLICATE KEY UPDATE
id=2

Source

  • I don’t want you to update, just don’t update if there is a record equal to the name = 'Joao', as in the case of Insert

  • Then all you have to do is equal the same Insert: update test set name = 'John' Where not exists(select * from test Where name = 'John')

  • Have you tried running on your Mysql? Here gives syntax error...

  • Actually I had not tested in mysql, in Firebird it works, but thinking about it, why would you need this command ? What is your need?

  • Check by name and phone if user is already registered, then need to check the update also right?

  • as I do in Insert... And I did not intend to make a select before, to do the check, do you understand? Utilizo PHP.

  • In this case it will not be possible, you will actually have to select before searching for the id for example and if it is not null (using the user and password as filter), it would not exist in your register.

Show 2 more comments

1


What is not very clear in the question is that you want to prevent an update from generating a duplicate name in the table, it’s not even?

The most correct way to do this is to create a unique index for the field so that if there is any attempt to insert or update a name to a value that already exists the database will throw an error that you can then treat the way as well as understand.

If you really need a consultation, you can make a update with join, as follows:

update teste t1 
left outer join teste t2 
  on t2.nome = '<novo nome>'
  and t2.id != <id sendo atualizado>
set t1.nome = '<novo nome'>
where t1.id = <id sendo atualizado> and t2.id is null;

Basically what is done above is the following:

  • Cross the table with itself, but allow the second table to contain no record, which will signal that it does not have a duplicate name
  • From the second table, select records with the same name: `t2.name = ''
  • Except what’s being updated: t2.id != <id sendo atualizado>
  • Finally t2.id is null notes that in the join no record with the same name was found, therefore the outer join returns fields with null values.

See a full example:

create table teste (id int, nome varchar(100));

insert into teste (id, nome) values (1, 'Joao');
insert into teste (id, nome) values (2, 'Jose');
insert into teste (id, nome) values (3, 'Maria');

update teste t1 
left outer join teste t2 
  on t2.nome = 'Joao'
  and t2.id != 2
set t1.nome = 'Joao' 
where t1.id = 2 and t2.id is null;

update teste t1 
left outer join teste t2 
  on t2.nome = 'Joao2'
  and t2.id != 3
set t1.nome = 'Joao2' 
where t1.id = 3 and t2.id is null;

The first update tries to update Jose for the value Joao, but does nothing because there is already another record.

The second update tries to update Maria for Jose2, what works properly.

The final result is:

1 John

2 Jose

3 John 2

See the functional example in Sqlfiddle.

Browser other questions tagged mysql sql

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