Posted by Norman Peelman on 02/21/07 23:49

Captain Paralytic wrote:
> On 21 Feb, 11:24, Norman Peelman <npeel...@cfl.rr.com> wrote:
>> Captain Paralytic wrote:
>>> On 21 Feb, 04:10, Norman Peelman <npeel...@cfl.rr.com> wrote:
>>>> David Smithz wrote:
>>>>> "Paul Lautman" <paul.laut...@btinternet.com> wrote in message
>>>>> news:5416e9F1ug8raU1@mid.individual.net...
>>>>> In fact I have now managed to try it on an isolated row that I fixed
>>>>> afterwards and unfortunately it works how I don't want it to. Therefore both
>>>>> the fields become the same value.
>>>>> So how can I achieve this?
>>>> David,
>>>> Use either multiple queries or user-defined variables. Try this
>>>> (given a table with fields `A` and `B`:
>>>> UPDATE tablename SET A = B, B := @tmp WHERE @tmp := A
>>>> Nice one liner SQL that swaps two fields. :)
>>>> Norm
>>>> ...the WHERE clause gets evaluated first.
>>> I tried
>>> UPDATE `cars`
>>> SET `car` = `state`,
>>> `state` = @A
>>> WHERE @A := `car`
>>> and no rows were affected.
>> Because:
>>
>> UPDATE `cars` SET `car` = `state`, `state` := @A WHERE @A := `car`
>>
>> When using the SET command (at the mysql prompt) you can use either '='
>> or ':=', anywhere else you need ':='. You just have a typo in the sql.
>>
>> Norm- Hide quoted text -
>>
>> - Show quoted text -
>
> I still get 0 rows updated
>

Not sure what you're doing wrong. Although either way works, I
actually like the later style better - with one modification:

update tablename set A = (@tmp := A), A = B, B = @tmp

....just a little more readable as it is now obvious that the stuff
inside '( ... )' gets evaluated first and it removes the assignment from
the WHERE clause.

Norm

[Back to original message]