Compare 2 tables with condition NOT IN doesnt work..

madkitty · #1 (**permalink**) 06-01-11, 10:55

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?

r937 · #2 (**permalink**) 06-01-11, 12:36

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

madkitty · #3 (**permalink**) 06-01-11, 22:24

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..

r937 · #4 (**permalink**) 06-02-11, 01:38

Quote:

Originally Posted by madkitty

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

Internet Services

Web Development

Digital Marketing

Consumer Tech