maria-discuss team mailing list archive

[Roberto Spadim <roberto@xxxxxxxxxxxxx>]

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@xxxxxxxxxxxxx>:

> 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@xxxxxxxxxx>:
>
> 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@xxxxxxxxxxxxx>
>> 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@xxxxxxxxxx>:
>> >
>> >> On Wed, Jun 11, 2014 at 8:32 PM, Roberto Spadim <roberto@xxxxxxxxxxxxx
>> >
>> >> 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