Results 1 to 5 of 5
  1. #1
    Join Date
    Apr 2011
    Posts
    18

    Question Unanswered: Reverse content of table with condition

    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);


  2. #2
    Join Date
    Sep 2009
    Location
    San Sebastian, Spain
    Posts
    880
    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)
    Ronan Cashell
    Certified Oracle DBA/Certified MySQL Expert (DBA & Cluster DBA)
    http://www.it-iss.com
    Follow me on Twitter

  3. #3
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    ronan, he wanted to update the table and switch the values

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    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!!
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    Apr 2011
    Posts
    18
    Code:
    UPDATE switcheroo 
       SET col2 = (@temp:=col2)
         , col2 = col3
         , col3 = @temp
     WHERE col1 = '-'
    WOW It works like a charm
    Thanks a bunch

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •