Swapping values in mysql fields - possible (easy)?

Hi

I have an mysql table.

For particular set of rows I want to swap two of the values.

So it would be something like

update exampletable set
field1= field 2
field 2= field1
where field3=value

Is this possible? I was worried that doing this might make both the values
the same because by time you get to
field2 = field1, the value in field 1 has changed and that data is lost?

this is using mysql.

Thank in advance for any input.

Re: Swapping values in mysql fields - possible (easy)?

David Smithz wrote:
> Hi
>
> I have an mysql table.
>
> For particular set of rows I want to swap two of the values.
>
> So it would be something like
>
> update exampletable set
> field1= field 2
> field 2= field1
> where field3=value
>
> Is this possible? I was worried that doing this might make both the
> values the same because by time you get to
> field2 = field1, the value in field 1 has changed and that data is
> lost?
> this is using mysql.
>
> Thank in advance for any input.

If you are worried, why not try it out on a test table and see what happens?

Re: Swapping values in mysql fields - possible (easy)?

"Paul Lautman" wrote in message
news:5416e9F1ug8raU1@mid.individual.net...
>
> If you are worried, why not try it out on a test table and see what
> happens?

don't have much access to a server to test it. Also worried the test server
I have is a different version to the live server. It's not something I
normally do but wanted a bit of reassurance.

Re: Swapping values in mysql fields - possible (easy)?

"Paul Lautman" 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?

Re: Swapping values in mysql fields - possible (easy)?

David Smithz wrote:
> "Paul Lautman" 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.

Re: Swapping values in mysql fields - possible (easy)?

On 21 Feb, 01:17, "David Smithz" wrote:
> "Paul Lautman" wrote in message
>
> news:5416e9F1ug8raU1@mid.individual.net...
>
>
>
> > If you are worried, why not try it out on a test table and see what
> > happens?
>
> don't have much access to a server to test it. Also worried the test server
> I have is a different version to the live server. It's not something I
> normally do but wanted a bit of reassurance.

Get an account at www.db4free.net, that's where I do all of my testing.

Re: Swapping values in mysql fields - possible (easy)?

On 21 Feb, 04:10, Norman Peelman wrote:
> David Smithz wrote:
> > "Paul Lautman" 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.

Re: Swapping values in mysql fields - possible (easy)?

On 21 Feb, 02:00, "David Smithz" wrote:
> "Paul Lautman" 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?

This one works

UPDATE `exampletable`
SET `field1` = @tmp := `field1`, `field1` = `field2`,
`field2` = @tmp
WHERE `field3` = value

Re: Swapping values in mysql fields - possible (easy)?

Captain Paralytic wrote:
> On 21 Feb, 04:10, Norman Peelman wrote:
>> David Smithz wrote:
>>> "Paul Lautman" 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

Re: Swapping values in mysql fields - possible (easy)?

Captain Paralytic wrote:
> On 21 Feb, 02:00, "David Smithz" wrote:
>> "Paul Lautman" 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?
>
> This one works
>
> UPDATE `exampletable`
> SET `field1` = @tmp := `field1`, `field1` = `field2`,
> `field2` = @tmp
> WHERE `field3` = value
>

They both work. And yes, another good example. You need to use := to
set the variable and = to read it.

Norm

Re: Swapping values in mysql fields - possible (easy)?

On 21 Feb, 11:24, Norman Peelman wrote:
> Captain Paralytic wrote:
> > On 21 Feb, 04:10, Norman Peelman wrote:
> >> David Smithz wrote:
> >>> "Paul Lautman" 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

Re: Swapping values in mysql fields - possible (easy)?

Captain Paralytic wrote:
> On 21 Feb, 11:24, Norman Peelman wrote:
>> Captain Paralytic wrote:
>>> On 21 Feb, 04:10, Norman Peelman wrote:
>>>> David Smithz wrote:
>>>>> "Paul Lautman" 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

Re: Swapping values in mysql fields - possible (easy)?

On 21 Feb, 23:49, Norman Peelman wrote:
> Captain Paralytic wrote:
> > On 21 Feb, 11:24, Norman Peelman wrote:
> >> Captain Paralytic wrote:
> >>> On 21 Feb, 04:10, Norman Peelman wrote:
> >>>> David Smithz wrote:
> >>>>> "Paul Lautman" 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.