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 <[email protected]>: > 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 <[email protected]>: > > 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 <[email protected]> >> 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 <[email protected]>: >> > >> >> On Wed, Jun 11, 2014 at 8:32 PM, Roberto Spadim <[email protected] >> > >> >> 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
_______________________________________________ Mailing list: https://launchpad.net/~maria-discuss Post to : [email protected] Unsubscribe : https://launchpad.net/~maria-discuss More help : https://help.launchpad.net/ListHelp
