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?
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?
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:
t2.id != <id sendo atualizado>
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