0

I have in my SQL table about 5000 rows. I have two columns 'name1' and 'name2'. And now, tricky part. I have to data from 'name1' put into 'name2' and data from 'name2' should be in 'name1'... I know that I can make extra column just for this action but I have a feeling that is a better and faster way to do it. Important part that I cannot do it for all the rows but for particular part (about 50%), simply for 'WHERE type = 'p' ' ;-)

Michal Olszowski
  • 795
  • 1
  • 8
  • 25
  • I deleted my answer following your clarification... – Vadim Jan 21 '14 at 12:33
  • Sorry that I firstly forgot about this part ;-) But your answer wasn't stupid. I just change columns names and later data from on of them just put into second (for this rows that I didn't want to affect second column was empty so I was able to do it). Although I'm still intereseting is there any way in SQL to make something like 'exchange' data from two columns ;-) – Michal Olszowski Jan 21 '14 at 12:36
  • have a look on this one. http://stackoverflow.com/questions/37649/swapping-column-values-in-mysql – I kiet Jan 21 '14 at 13:04

1 Answers1

1

Use case-when to conditionally rename the columns:

SELECT
    CASE WHEN t.type = 'p' THEN t.name2 ELSE t.name1 END as name1,
    CASE WHEN t.type = 'p' THEN t.name1 ELSE t.name2 END as name2,
    t.type
FROM (your original select) t;
Zbynek Vyskovsky - kvr000
  • 18,186
  • 3
  • 35
  • 43