Swap column values

• talbronstien

  Old Hand

  Points: 323

  More actions

  March 27, 2006 at 8:25 am

  #112529

  How can we swap values between 2 rows in one statement..i have 2 columns name and age..

  John,26

  Jame,32..I realised this is wrong john must be 32 and jame 26..how can I swap these values

  thks

• Phil Parkin

  SSC Guru

  Points: 246960

  More actions

  March 27, 2006 at 9:38 am

  #628962

  A strange request!

  update table set age = 32 where id = [PK for row containing John's data]

  update table set age = 26 where id = [PK for row containing Jame's data]

  There is no 'SWAP DATA' or equivalent command in SQL Server, so you can't easily do this in one statement (well, maybe with a CASE statement, but why not just use the front-end client app?).

  The absence of evidence is not evidence of absence
  - Martin Rees
  The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
  - Phil Parkin

• RyanRandall

  SSChampion

  Points: 13623

  More actions

  March 28, 2006 at 5:17 am

  #629119

  Just for fun...

  --data

  declare @t table (name varchar(10), age int)

  insert @t

            select 'John', 26

  union all select 'Jame', 32

  union all select 'Bob', 45

  --calculation

  update a set age = b.age from @t a, @t b where a.name in ('John', 'Jame') and a.age != b.age

  select * from @t

  But, yes, what an odd request!

  Ryan Randall

  Solutions are easy. Understanding the problem, now, that's the hard part.