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

    Question Unanswered: Compare 2 tables with condition NOT IN doesnt work..

    I have two tables that I have to compare.
    col1 stores integer from 1 to 22. col stores big integer and both are over 300Mb
    What happened is that tbl1 and tbl2 should be identical but at some point some integer got inserted into tbl2 and others were removed.. I need to figure out which one got inserted and removed.

    tbl1
    col1 | col2
    1 | 123456
    1 | 123457
    1 | 133333
    1 | 134333
    1 | 125555
    2 | 777
    2 | 779
    2 | 1000
    2 | 1100
    2 | 1200
    2 | 1500
    2 | 1555
    2 | 1577
    2 | 1777

    tbl2
    col1| col2
    1 | 123456
    1 | 123457
    1 | 123460
    1 | 133333
    1 | 134333
    1 | 125555
    2 | 777
    2 | 779
    2 | 1000
    2 | 1100
    2 | 1200
    2 | 1555
    2 | 1577
    2 | 1777

    (here as an example I added 123460 on the 3rd row and Deleted 1500 on the 11th row tbl1).

    I need to know HOW tbl2 is different than tbl1.

    Code:
    UPDATE table2 CROSS JOIN table1 ON 
    (tbl2.col1 = tbl1.col1 AND tbl2.col2 != tbl1.col2)
    SET tbl2.name = 'insertion';
    I tried this code but that crashed MySQL, I had to uninstall and re-install..


    Code:
    INSERT INTO tbl3
    SELECT tbl1.* FROM tbl1
    INNER JOIN tbl2 ON
    (tbl1.col1 = tbl2.col2 AND tbl1.col2 != tbl2.col2)
    And this one was running for ever...

    anyone has a suggestion?

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    not really

    we'll never understand your problem or your data if you keep hiding what you're doing behind generic meaningless names like tbl1, tbl2, col1, col2

    what the heck are you doing using a CROSS JOIN in an UPDATE for?

    and the INSERT appears to have nothing to do with the UPDATE

    it really appears like you are floundering

    and you're not letting us help you, by not revealing what your final purpose is
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Apr 2011
    Posts
    18
    Im a grad student in biochemistry with some knowledge in bio-informatic. Unfortunately nobody can't help me in my lab with MySQL but I need to go throu all these numbers, I can't tell/show too much content because I'm tight by confidentiality sorry AND I'm think that would be even more confusing than helping -.. Meanwhile any kind of help is always greatly appreciated

    Alright let's say that table 1 = cell 1
    table 2 = cell 2
    cell 1 and cell 2 are a clone of each other.
    col1 : chromosome number
    col2 : position on this chromosome.

    tbl1 (mother cell)
    chr | pos
    1 | 123456
    1 | 123457
    1 | 133333
    1 | 134333
    1 | 125555
    2 | 777
    2 | 779
    2 | 1000
    2 | 1100
    2 | 1200
    2 | 1500 (this one doesn't appear in the second cell, we call it a "deletion")
    2 | 1555
    2 | 1577
    2 | 1777

    tbl2 (daughter cell, clone of mother cell)
    chr | pos
    1 | 123456
    1 | 123457
    1 | 123460 (this one is a new position, we call it an "insert")
    1 | 133333
    1 | 134333
    1 | 125555
    2 | 777
    2 | 779
    2 | 1000
    2 | 1100
    2 | 1200
    2 | 1555
    2 | 1577
    2 | 1777

    For each chromosome (column 1) I need to see if some position got inserted or deleted. So in my exemple from cell 1 to cell 2 (tbl1 to tbl2) I added 123460 on the 3rd row and Deleted 1500 on the 11th row tbl1 (and see if the query works)

    TRY #1
    Code:
    UPDATE table2 CROSS JOIN table1 ON 
    (tbl2.col1 = tbl1.col1 AND tbl2.col2 != tbl1.col2)
    SET tbl2.name = 'insertion';
    Here I create a third column in table 2 (cell 2) where I wanted to write "insertion" everytime a new position was inserted. BUT this query didn't work out... So I tried differently..


    TRY #2
    Code:
    INSERT INTO tbl3
    SELECT tbl1.* FROM tbl1
    INNER JOIN tbl2 ON
    (tbl1.col1 = tbl2.col2 AND tbl1.col2 != tbl2.col2)

    In my second trial, I wanted to select every inserted/deleted position and put it in a third table ... but this query was running for ever.

    I hope this will be more helpful to understand where I'm struggling with MySQL..

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by madkitty View Post
    For each chromosome (column 1) I need to see if some position got inserted or deleted.
    let's focus on this requirement

    positions that got inserted in tbl2 --
    Code:
    SELECT tbl2.chr
         , tbl2.pos
      FROM tbl2
    LEFT OUTER
      JOIN tbl1
        ON tbl1.chr = tbl2.chr
       AND tbl1.pos = tbl2.pos
     WHERE tbl1.chr IS NULL
    positions that got deleted from tbl1 --
    Code:
    SELECT tbl1.chr
         , tbl1.pos
      FROM tbl1
    LEFT OUTER
      JOIN tbl2
        ON tbl2.chr = tbl1.chr
       AND tbl2.pos = tbl1.pos
     WHERE tbl2.chr IS NULL
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

Posting Permissions

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