Jump to content


Photo

Easy question, easy solution?


  • Please log in to reply
1 reply to this topic

#1 janroald

janroald
  • Members
  • PipPipPip
  • Advanced Member
  • 78 posts
  • LocationTrondheim, Norway

Posted 23 December 2005 - 04:00 PM

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?


#2 fenway

fenway
  • Staff Alumni
  • MySQL Si-Fu / PHP Resident Alien
  • 16,199 posts
  • LocationToronto, ON

Posted 23 December 2005 - 09:42 PM

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?
Seriously... if people don't start reading this before posting, I'm going to consider not answering at all.




0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users