alter table test engine=innodb; begin; update test set id = case when id = '1' then '2' when id = '2' then '1' end; commit; /* Erro SQL (1062): Duplicate entry '2' for key 'PRIMARY' */ 2014-06-12 1:28 GMT-03:00 Roberto Spadim <roberto@spadim.com.br>:
aria and myisam don't support
2014-06-12 1:27 GMT-03:00 Roberto Spadim <roberto@spadim.com.br>:
update test set id = case
when id = '1' then '2' when id = '2' then '1' end; /* Erro SQL (1062): Duplicate entry '2' for key 'PRIMARY' */
2014-06-12 1:26 GMT-03:00 Roberto Spadim <roberto@spadim.com.br>:
that's the point, i think aria have a log before commit, and myisam
commit at everychange must check, maybe the only possible method is transactional or aria, i will try
2014-06-12 0:57 GMT-03:00 Pavel Ivanov <pivanof@google.com>:
I think you need to explain better what the big task is. This
particular problem is solved with this:
update test set other = case when other = 'a' then 'b' when other = 'b' then 'a' end;
I wonder though how would this be different from two different updates if MySQL gets interrupted after updating one row, but before updating the second one. Or if it successfully updates first row, but gets some error with the second one. MyISAM/Aria can't rollback the first row, can it?
On Wed, Jun 11, 2014 at 8:48 PM, Roberto Spadim <roberto@spadim.com.br> wrote:
ok it with a innodb/transactional table what about a aria/myisam/connect/federated table?
2014-06-12 0:45 GMT-03:00 Pavel Ivanov <pivanof@google.com>:
On Wed, Jun 11, 2014 at 8:32 PM, Roberto Spadim < roberto@spadim.com.br> wrote: > Guys, i'm with a "newbie" question > I need to swap a primary key value, for example: > create table test ( > id int not null default 0, > other varchar(255) not null default '', > primary key(id) > ); > insert into test (1,'a'); > insert into test (2,'b'); > > now i want that (2,'b') becomes (1,'b') and (1,'a') becomes (2,'a') > > the point is, how to do this, with only one UPDATE without duplicate > column > id value? and without delete values?
How about this:
begin; update test set other = 'b' where id = 1; update test set other = 'a' where id = 2; end;
-- Roberto Spadim SPAEmpresarial Eng. Automação e Controle
-- Roberto Spadim SPAEmpresarial Eng. Automação e Controle
-- Roberto Spadim SPAEmpresarial Eng. Automação e Controle
-- Roberto Spadim SPAEmpresarial Eng. Automação e Controle
-- Roberto Spadim SPAEmpresarial Eng. Automação e Controle