maria-discuss team mailing list archive

[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