Easy question, easy solution?

[janroald]

Two rows : 1&2, values x&y

 

id | value

1 (x)

2 (y)

 

query = "make value in row with id 1 like the value in row with id 2 and vice verca"

 

resulting

 

id | value

1 (y)

2 (x)

 

This is easy to do with two queries but how do you do it in one? Its probably easy, but I can't seem to find the solution in either mysqldoc or by googling.

 

Anyone?

 

[fenway]

It's not easy at all -- in fact, it's almost impossible. You can't set user variables in an UPDATE statement, so doing something simple like "@temp = t2, t2 = t1, t1 = @temp" doesn't work; you also can't use a subquery in an UPDATE statement if it's using the same table. There are very contrived and complicated ways to do this -- simply using a second query is much more logical. It's possible that using SPs you could do, but why bother going this route?