Posted by Captain Paralytic on 02/21/07 13:03

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

[Back to original message]