Reverse content of table with condition

madkitty · #1 (**permalink**) 05-25-11, 05:29

Hi there,

I have a table that looks like that:
(+or-) (smaller number) (bigger number)
col1 col2 col3
+ 100 115
- 155 116
+ 225 250
- 300 275
+ 325 350

Everytime that we have '-' in col1 i need to reverse the number col2<-->col3

I created a temporary table called INVERSE(with 2 columns a and b) to store numbers temporarily in it and then paste them in the right column. But my query doesn't work... How can I simply reverse the content

Code:

mysql> SELECT chr.start, chr.end FROM chr
    -> JOIN chr start ON
    -> (chr.start = chr.end AND chr.end = chr.start)
    -> WHERE strand='-'
    -> SET (chr.start=inverse.2 AND chr.end=inverse.1);

it-iss.com · #2 (**permalink**) 05-25-11, 12:05

Have you tried using IF statements:

SELECT IF(col1="-",col3,col2) firstcol, IF(col1="-",col2,col3) secondcol
FROM table;

Like this:

mysql> select * from t1;
+------+------+------+
| col1 | col2 | col3 |
+------+------+------+
| + | 100 | 115 |
| - | 155 | 116 |
| + | 225 | 250 |
| - | 300 | 275 |
| + | 325 | 350 |
+------+------+------+
5 rows in set (0.00 sec)

mysql> select if(col1='-',col3,col2) first, if(col1='-',col2,col3) second
-> from t1;
+-------+--------+
| first | second |
+-------+--------+
| 100 | 115 |
| 116 | 155 |
| 225 | 250 |
| 275 | 300 |
| 325 | 350 |
+-------+--------+
5 rows in set (0.00 sec)

r937 · #3 (**permalink**) 05-25-11, 12:34

ronan, he wanted to update the table and switch the values

r937 · #4 (**permalink**) 05-25-11, 12:51

Code:

CREATE TABLE switcheroo
( col1 CHAR(1)
, col2 INTEGER
, col3 INTEGER
);
INSERT INTO switcheroo VALUES
 ('+',100,115)
,('-',155,116)
,('+',225,250)
,('-',300,275)
,('+',325,350)
;
UPDATE switcheroo 
   SET col2 = (@temp:=col2)
     , col2 = col3
     , col3 = @temp
 WHERE col1 = '-'
;
SELECT * FROM switcheroo
;

vwalah!!

madkitty · #5 (**permalink**) 05-25-11, 21:59

Code:

UPDATE switcheroo 
   SET col2 = (@temp:=col2)
     , col2 = col3
     , col3 = @temp
 WHERE col1 = '-'

WOW It works like a charm

Thanks a bunch

Internet Services

Web Development

Digital Marketing

Consumer Tech