Posted by Captain Paralytic on 02/22/07 10:10

On 21 Feb, 23:49, Norman Peelman <npeel...@cfl.rr.com> wrote:
> 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- Hide quoted text -
>
> - Show quoted text -

I actually had the braqckets in it when I first did it. Then I decided
to take 'em out. Dunno why, 'cos I agree they make it plainer what is
happening.

[Back to original message]