NOT EXISTS in update?

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?

    
asked by anonymous 18.06.2016 / 01:37

2 answers

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, right?

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 handle as you see fit.

If you really need a query, you can 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 as follows:

  • Cross the table with itself, however allowing the second table to contain no records, 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 is being updated: t2.id != <id sendo atualizado>
  • Finally t2.id is null verifies that no join has not found any record with the same name, therefore outer join returns fields with null values.

See a complete 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 attempts to update Jose to the value Joao , but does nothing because another record already exists.

The second update tries to update Maria to Jose2 , which works correctly.

The end result is:

  

1 John

     

2 Jose

     

3 Joao2

See the functional example in SQLFiddle.

    
24.06.2016 / 10:42
3

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

Font

    
18.06.2016 / 22:20