maria-discuss team mailing list archive

[Pavel Ivanov <pivanof@xxxxxxxxxx>]

Note, that I didn't say to change id, I said to change 'other'.

On Wed, Jun 11, 2014 at 9:30 PM, Roberto Spadim <roberto@xxxxxxxxxxxxx> wrote:
> i think neither the first idea should work...
>
> alter table test engine=innodb;
> begin;
> update test set id = 1 where id=2;
>
> /* Erro SQL (1062): Duplicate entry '1' for key 'PRIMARY' */
> update test set id = 2 where id=1;
> commit;
>
>
> 2014-06-12 1:29 GMT-03:00 Roberto Spadim <roberto@xxxxxxxxxxxxx>:
>
>> 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@xxxxxxxxxxxxx>:
>>
>>> aria and myisam don't support
>>>
>>>
>>> 2014-06-12 1:27 GMT-03:00 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
>>>
>>>
>>>
>>>
>>> --
>>> Roberto Spadim
>>> SPAEmpresarial
>>> Eng. Automação e Controle
>>
>>
>>
>>
>> --
>> Roberto Spadim
>> SPAEmpresarial
>> Eng. Automação e Controle
>
>
>
>
> --
> Roberto Spadim
> SPAEmpresarial
> Eng. Automação e Controle