Posted by Norman Peelman on 02/21/07 11:24

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

[Back to original message]